Date Formatting in SQL Server

SQL Server provides multiple approaches for date formatting and manipulation. Traditional methods like CONVERT() coexist with modern functions such as FORMAT(), offering developers flexibility in handling temporal data.

Traditional CONVERT() Function

The CONVERT() function controls date display through style parameters, though numeric styles can be challenging to memorize:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Common Asian-friendly formats include:

• 101: mm/dd/yyyy
• 110: mm-dd-yyyy
• 111: yyyy/mm/dd
• 112: yyyymmdd
• 120: yyyy-mm-dd hh:mm:ss
• 121: yyyy-mm-dd hh:mm:sssssss

Modern FORMAT() Function

The FORMAT() function offers greater flexibility for formatting dates, times, and numeric values:

FORMAT ( value, format [, culture ] ) 

Date/time placeholders include:

• yyyy, MM, dd for year, month, day
• hh:mm:ss fffffff for hour, minute, second, millisecond
• Separators like "/" and "-" for component division

Example usage:

SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd hh:mm:ss fffffff')
SELECT FORMAT(123456789, '###-##-####') AS 'Formatted Number'

Date Component Extraction

Key date components include: year, month, day, hour, minute, second, nanosecond, and timezone offset (tz).

DATEPART ( datepart , date ) 
YEAR ( date )  
MONTH ( date )  
DAY ( date ) 

Additional useful components:

• quarter: quarters 1-4
• week: week number 1-53
• dayofyear: day of year 1-366
• weekday: day of week 1-7

For string results, use DATENAME():

DATENAME ( datepart , date ) 

Date construction functions:

DATEFROMPARTS ( year, month, day )
DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision ) 
DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )  

Date Manipulation Functions

EOMONTH() for Month End Calculation

Returns the last day of a specified month:

EOMONTH ( start_date [, month_to_add ] )

Example:

DECLARE @current_date DATE = GETDATE()

SELECT EOMONTH(@current_date) AS CurrentMonthEnd,
       EOMONTH(@current_date, 1) AS NextMonthEnd,
       EOMONTH(@current_date, -1) AS PreviousMonthEnd

First Day of Month

Using DATEFROMPARTS():

DECLARE @today DATE = GETDATE()
SELECT DATEFROMPARTS(YEAR(@today), MONTH(@today), 1) 

Using FORMAT():

SELECT FORMAT(GETDATE(), 'yyyyMM01')

Timezone Conversion

SWITCHOFFSET() converts DateTimeOffset to different timezones:

SWITCHOFFSET ( DATETIMEOFFSET, time_zone )  

Example converting from UTC+8 to UTC+7:

DECLARE @local_time DATETIMEOFFSET = SYSDATETIMEOFFSET()
DECLARE @converted_time DATETIMEOFFSET = SWITCHOFFSET(@local_time, '+07:00')
SELECT @converted_time AS converted, @local_time AS original

Weekday Detection

Setting first day of week:

SET DATEFIRST { number | @number_var }

Getting current setting:

SET DATEFIRST 1
SELECT @@DATEFIRST

Weekday calculation considering DATEFIRST:

SET DATEFIRST 2
SELECT DATEPART(WEEKDAY, GETDATE() + @@DATEFIRST - 1)

Weekday Names

Language-dependent weekday names:

SET LANGUAGE 'Simplified Chinese'
SELECT DATENAME(WEEKDAY, GETUTCDATE())

SET LANGUAGE 'us_english'
SELECT DATENAME(WEEKDAY, GETUTCDATE())

Core Date Functions Reference

Function	Parameters	Example	Description
DATEADD	(datepart, number, date)	DATEADD(year, 45, '1990-12-11')	Adds interval to date
DATEDIFF	(datepart, start_date, end_date)	DATEDIFF(yy, '1990-12-11', '2008-9-10')	Calculates date difference
DATENAME	(datepart, date)	DATENAME(mm, '2007-12-11')	Returns date part as string
DATEPART	(datepart, date)	DATEPART(mm, '2007-12-11')	Returns date part as integer
GETDATE	()	GETDATE()	Returns current system datetime
GETUTCDATE	()	GETUTCDATE()	Returns current UTC datetime

Common Date Calculations

Previous month start:

SELECT CONVERT(CHAR(10), DATEADD(month, -1, DATEADD(dd, -DAY(GETDATE()) + 1, GETDATE())), 111)

Previous month end:

SELECT CONVERT(CHAR(10), DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)), 111) + ' 23:59:59'

Current month start:

SELECT CONVERT(CHAR(10), DATEADD(dd, -DAY(GETDATE()) + 1, GETDATE()), 111)

Current month end:

SELECT CONVERT(CHAR(10), DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)), 111) + ' 23:59:59'

Date Range Queries

Today's records:

SELECT * FROM table_name WHERE DATEDIFF(day, date_column, GETDATE()) = 0

Last 30 days:

SELECT * FROM table_name WHERE DATEDIFF(d, date_column, GETDATE()) <= 30

This week:

SELECT * FROM table_name WHERE DATEDIFF(week, date_column, GETDATE()) = 0

This month:

SELECT * FROM table_name WHERE DATEDIFF(month, date_column, GETDATE()) = 0

This quarter:

SELECT * FROM table_name WHERE DATEDIFF(qq, date_column, GETDATE()) = 0