TSQL Select dateformat?

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;

Any ideas?

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

Actually that isn't what I was looking for.

If I am to insert '2018-01-02' I want to know, with complete confidence it is yyyy-mm-dd on this DB instance.

Maybe there is a setting or property I can view.

If you do a select getdate() its in that format.
image

if you take out the hyphens, it's ISO format yyyymmdd

e.g, 20180102 = 2 Jan 2018, everywhere!

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.

Maybe what you want is https://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/

1 Like

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)
;

How do I know, when I am inserting '20180102' where I put the day / month?

When executing

select name ,alias, dateformat
from syslanguages
where langid =
(select value from master..sysconfigures
where comment = 'default language')

I get
|name|alias|dateformat|
|us_english|English|mdy|

This to me is saying, I should be formating my insert statements using
01022018
MMDDYYY
to ensure I always save the date correctly.

20180102 is always yyyymmdd. that's how it's defined

No sir... You should always use ISO base dates, which are in the format of YYYYMMDD without any dashes or other separators in T-SQL.

Thank you.