I am trying to find out what the default saved datetime format is.
I know these two functions help, but I also want to see the exact format it is saving in.
select SYSDATETIME()
select @@LANGUAGE
I know i can change it using
SET LANGUAGE us_english;
SET DATEFORMAT mdy;
SQL Server stores datetime values in a proprietary format on disk that is agnostic to locale and the DATETIMEFORMAT setting. You can see it if you really want to by looking at data pages with a hex editor
Thanks for the response. I have been using this method, but would still like to know if there is a setting that would show me the format, such as within DB Settings, or ?
In a way, the question is ambigous. The internal storage is proprietary. The way it is presented can be controlled by CONVERT. In SSMS the default will depend on your locale settings, which can be changed at run time. An application program (e.g. C#) should set the locale properly or use CONVERT to get the desired format. If it's being mapped to a .NET DateTime object, it should be correct no matter what.
If you want "complete confidence", then NEVER trust defaults, period! Here's an example of what can happen when you do...
SET LANGUAGE 'ENGLISH';
SELECT CONVERT(DATETIME,'2018-01-02')
;
SET LANGUAGE 'FRENCH';
SELECT CONVERT(DATETIME,'2018-01-02')
;
SELECT CONVERT(DATETIME,'2018-01-02',120)
;