IT:AD:SQL Server:HowTo:SQL Functions/Date Functions

Sql Server works with dates, such as when converting from/to strings, depending on @@LANGUAGE.

Since the default language is us-english, @@DATEFORMAT is equal to mdy – so the result is by default pretty unportable.

A way to correct¹⁾ that is:

-- Valid values are mdy, dmy, ymd, ydm, myd, and dym
SET DATEFORMAT ymd.

Another Date variable affected by @@LANGUAGE is the @@DATEFIRST which determines what is the first day of the week (us = sunday):

SELECT @@LANGUAGE       -- Returns us_english
SELECT DATEFIRST        -- Returns 7 (SUN). Notice no quotes or anything...

SET LANGUAGE italian
SELECT @@DATEFIRST      -- Returns 1 (MON)

set LANGUAGE us_english
SELECT @@DATEFIRST      --Returns 7 (SUN)

Use GETDATE(), GETUTCDATE() – or the more precise SYSDATETIME() and SYSUTCDATETIME()

<div info>

• @@DATEFORMAT: http://msdn.microsoft.com/en-us/library/ms189491.aspx
• @@DATEFIRST: http://msdn.microsoft.com/en-us/library/ms187766.aspx
• CONVERT: http://msdn.microsoft.com/en-us/library/ms187928.aspx
• DATEPART: http://msdn.microsoft.com/en-us/library/ms174420.aspx