Date format statement in SQL Server

The set dateformat  statement defines the order of datepart (Month, day , Year) when interpreting string date value in SQL Server. This setting is crucial for ensuring that date strings are interpreted correctly especially when dealing with different regional date format.

Syntex

Set DATEFORMAT {format}

*Format can be mdy,dmy,ydm,myd or dym

The US English default dateformat is MDY.

Available Date format

Format	Order	Example(’04-12-2024’)
MDY	Month-Day_Year	April 12 2024(04-12-2024)
DMY	Day-month-year	12 April 2024 (12-04-2024)
YMD	Year- month- day	2024 April 12 (2024-04-12)
YDM	Year-day-Month	2024 12 April (2024-12-04)
MYD	Month-year-day	April 2024, 12 (04-2024-12)
DYM	Day-year-month	12th 2024 April (12-2024-04)

 See the example

set DATEFORMAT MDY SELECT CAST('04/05/2025' AS DATE); -- Interpreted as April 5, 2025  -- But if you meant 4 May 2025, SET DATEFORMAT DMY; SELECT CAST('04/05/2025' AS DATE); -- Now it's 4 May 2025

 

Data Types for Date and Time

We have the following SQL convert date and Time data types in SQL Server.

Date type	Format
Time	hh:mm:ss[.nnnnnnn]
Date	YYYY-MM-DD
SmallDateTime	YYYY-MM-DD hh:mm:ss
DateTime	YYYY-MM-DD hh:mm:ss[.nnn]
DateTime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]
DateTimeOffset	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm

 Below are the built-in functions which provide server date and format in various formats.

Ø  SYSDATETIME (): The SYSDATETIME () function returns the current system date and time with precision to fractions of a second as a datetime2 data type.

Ø  SYSDATETIMEOFFSET (): The SYSDATETIMEOFFSET () function returns the current date and time of the system including the time zone offset from UTC. It’s useful when you need date-time with time zone awareness.

Ø  GETUTCDATE (): The GETUTCDATE () function returns the current date and time in Coordinated Universal Time (UTC).

Ø  GETDATE (): In SQL Server, GETDATE () is a built-in function that returns the current system date and time based on the server's local time zone.

Ø  CURRENT_TIMESTAMP: In SQL Server, CURRENT_TIMESTAMP is a built-in ANSI SQL-compliant function that returns the current date and time just like GETDATE ()

See the example

select SYSDATETIME()                As 'SYSDATETIME' select SYSDATETIMEOFFSET() As 'SYSDATETIMEOFFSET' select GETUTCDATE()                               As 'GETUTCDATE' select GETDATE()                                       AS 'GETDATE' select CURRENT_TIMESTAMP As 'CURRENT_TIMESTAMP'

 

Function	Type	Includes Time Zone	Precision
GETDATE()	datetime	❌ No	Milliseconds
SYSDATETIME()	datetime2(7)	❌ No	Milliseconds
SYSUTCDATETIME()	datetime2(7)	❌ (UTC only)	Milliseconds
SYSDATETIMEOFFSET()	datetimeoffset	✅ Yes	100 nanoseconds
CURRENT_TIMESTAMP	datetime	❌ No	Milliseconds