# Dates

# Date & Time Formatting using CONVERT

You can use the CONVERT function to cast a datetime datatype to a formatted string.

SELECT GETDATE() AS [Result]                                -- 2016-07-21 07:56:10.927
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),100) AS [Result] -- Jul 21 2016  7:56AM
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),101) AS [Result] -- 07/21/2016
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),102) AS [Result] -- 2016.07.21
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),103) AS [Result] -- 21/07/2016
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),104) AS [Result] -- 21.07.2016
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),105) AS [Result] -- 21-07-2016
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),106) AS [Result] -- 21 Jul 2016
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),107) AS [Result] -- Jul 21, 2016
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),108) AS [Result] -- 07:57:05
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),109) AS [Result] -- Jul 21 2016  7:57:45:707AM
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),110) AS [Result] -- 07-21-2016
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),111) AS [Result] -- 2016/07/21
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),112) AS [Result] -- 20160721
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),113) AS [Result] -- 21 Jul 2016 07:57:59:553
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),114) AS [Result] -- 07:57:59:553
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),120) AS [Result] -- 2016-07-21 07:57:59
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),121) AS [Result] -- 2016-07-21 07:57:59.553
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),126) AS [Result] -- 2016-07-21T07:58:34.340
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),127) AS [Result] -- 2016-07-21T07:58:34.340
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),130) AS [Result] -- 16 ???? 1437  7:58:34:340AM
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),131) AS [Result] -- 16/10/1437  7:58:34:340AM

# Date & Time Formatting using FORMAT

You can utilize the new function: FORMAT() (opens new window).

Using this you can transform your DATETIME fields to your own custom VARCHAR format.

Example

DECLARE @Date DATETIME = '2016-09-05 00:01:02.333'

SELECT FORMAT(@Date, N'dddd, MMMM dd, yyyy hh:mm:ss tt')

Monday, September 05, 2016 12:01:02 AM

Arguments

Given the DATETIME being formatted is 2016-09-05 00:01:02.333, the following chart shows what their output would be for the provided argument.

Argument Output
yyyy 2016
yy 16
MMMM September
MM 09
M 9
dddd Monday
ddd Mon
dd 05
d 5
HH 00
H 0
hh 12
h 12
mm 01
m 1
ss 02
s 2
tt AM
t A
fff 333
ff 33
f 3

You can also supply a single argument to the FORMAT() function to generate a pre-formatted output:

DECLARE @Date DATETIME = '2016-09-05 00:01:02.333'

SELECT FORMAT(@Date, N'U')

Monday, September 05, 2016 4:01:02 AM

Single Argument Output
D Monday, September 05, 2016
d 9/5/2016
F Monday, September 05, 2016 12:01:02 AM
f Monday, September 05, 2016 12:01 AM
G 9/5/2016 12:01:02 AM
g 9/5/2016 12:01 AM
M September 05
O 2016-09-05T00:01:02.3330000
R Mon, 05 Sep 2016 00:01:02 GMT
s 2016-09-05T00:01:02
T 12:01:02 AM
t 12:01 AM
U Monday, September 05, 2016 4:01:02 AM
u 2016-09-05 00:01:02Z
Y September, 2016

Note: The above list is using the en-US culture. A different culture can be specified for the FORMAT() via the third parameter:

DECLARE @Date DATETIME = '2016-09-05 00:01:02.333'

SELECT FORMAT(@Date, N'U', 'zh-cn')

2016年9月5日 4:01:02

# DATEADD for adding and subtracting time periods

General syntax:

DATEADD (datepart , number , datetime_expr)  

To add a time measure, the number must be positive. To subtract a time measure, the number must be negative.

Examples

DECLARE @now DATETIME2 = GETDATE();
SELECT @now;                        --2016-07-21 14:39:46.4170000
SELECT DATEADD(YEAR, 1, @now)       --2017-07-21 14:39:46.4170000
SELECT DATEADD(QUARTER, 1, @now)    --2016-10-21 14:39:46.4170000
SELECT DATEADD(WEEK, 1, @now)       --2016-07-28 14:39:46.4170000
SELECT DATEADD(DAY, 1, @now)        --2016-07-22 14:39:46.4170000
SELECT DATEADD(HOUR, 1, @now)       --2016-07-21 15:39:46.4170000
SELECT DATEADD(MINUTE, 1, @now)     --2016-07-21 14:40:46.4170000
SELECT DATEADD(SECOND, 1, @now)     --2016-07-21 14:39:47.4170000
SELECT DATEADD(MILLISECOND, 1, @now)--2016-07-21 14:39:46.4180000

NOTE: DATEADD also accepts abbreviations in the datepart parameter. Use of these abbreviations is generally discouraged as they can be confusing (m vs mi, ww vs w, etc.).

# Get the current DateTime

The built-in functions GETDATE (opens new window) and GETUTCDATE (opens new window) each return the current date and time without a time zone offset.

The return value of both functions is based on the operating system of the computer on which the instance of SQL Server is running.

The return value of GETDATE represents the current time in the same timezone as operating system. The return value of GETUTCDATE represents the current UTC time.

Either function can be included in the SELECT clause of a query or as part of boolean expression in the WHERE clause.

Examples:

-- example query that selects the current time in both the server time zone and UTC
SELECT GETDATE() as SystemDateTime, GETUTCDATE() as UTCDateTime

-- example query records with EventDate in the past.
SELECT * FROM MyEvents WHERE EventDate < GETDATE() 

There are a few other built-in functions that return different variations of the current date-time:

SELECT 
    GETDATE(),          --2016-07-21 14:27:37.447
    GETUTCDATE(),       --2016-07-21 18:27:37.447
    CURRENT_TIMESTAMP,  --2016-07-21 14:27:37.447
    SYSDATETIME(),      --2016-07-21 14:27:37.4485768
    SYSDATETIMEOFFSET(),--2016-07-21 14:27:37.4485768 -04:00
    SYSUTCDATETIME()    --2016-07-21 18:27:37.4485768

# Getting the last day of a month

Using the DATEADD and DATEDIFF functions, it's possible to return the last date of a month.

SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, '2016-09-23') + 1, 0))
-- 2016-09-30 00:00:00.000

The EOMONTH function provides a more concise way to return the last date of a month, and has an optional parameter to offset the month.

SELECT EOMONTH('2016-07-21')        --2016-07-31
SELECT EOMONTH('2016-07-21', 4)     --2016-11-30
SELECT EOMONTH('2016-07-21', -5)    --2016-02-29

# Create function to calculate a person's age on a specific date

This function will take 2 datetime parameters, the DOB, and a date to check the age at


 CREATE FUNCTION [dbo].[Calc_Age]
    (
    @DOB datetime , @calcDate datetime 
    )
    RETURNS int
    AS
   BEGIN
declare @age int

IF (@calcDate < @DOB  )
RETURN -1

-- If a DOB is supplied after the comparison date, then return -1
SELECT @age = YEAR(@calcDate) - YEAR(@DOB) +
  CASE WHEN DATEADD(year,YEAR(@calcDate) - YEAR(@DOB)
  ,@DOB) > @calcDate THEN -1 ELSE 0 END
    

RETURN @age
    

END

eg to check the age today of someone born on 1/1/2000

SELECT  dbo.Calc_Age('2000-01-01',Getdate())

# DATEDIFF for calculating time period differences

General syntax:

DATEDIFF (datepart, datetime_expr1, datetime_expr2)

It will return a positive number if datetime_expr is in the past relative to datetime_expr2, and a negative number otherwise.

Examples

DECLARE @now DATETIME2 = GETDATE();
DECLARE @oneYearAgo DATETIME2 = DATEADD(YEAR, -1, @now);
SELECT @now                                    --2016-07-21 14:49:50.9800000
SELECT @oneYearAgo                             --2015-07-21 14:49:50.9800000
SELECT DATEDIFF(YEAR, @oneYearAgo, @now)       --1
SELECT DATEDIFF(QUARTER, @oneYearAgo, @now)    --4
SELECT DATEDIFF(WEEK, @oneYearAgo, @now)       --52
SELECT DATEDIFF(DAY, @oneYearAgo, @now)        --366
SELECT DATEDIFF(HOUR, @oneYearAgo, @now)       --8784
SELECT DATEDIFF(MINUTE, @oneYearAgo, @now)     --527040
SELECT DATEDIFF(SECOND, @oneYearAgo, @now)     --31622400

NOTE: DATEDIFF also accepts abbreviations in the datepart parameter. Use of these abbreviations is generally discouraged as they can be confusing (m vs mi, ww vs w, etc.).

DATEDIFF can also be used to determine the offset between UTC and the local time of the SQL Server. The following statement can be used to calculate the offset between UTC and local time (including timezone).

select  DATEDIFF(hh, getutcdate(), getdate()) as 'CentralTimeOffset'

# DATEPART & DATENAME

DATEPART returns the specified datepart of the specified datetime expression as a numeric value.

DATENAME returns a character string that represents the specified datepart of the specified date. In practice DATENAME is mostly useful for getting the name of the month or the day of the week.

There are also some shorthand functions to get the year, month or day of a datetime expression, which behave like DATEPART with their respective datepart units.

Syntax:

DATEPART ( datepart , datetime_expr )
DATENAME ( datepart , datetime_expr )
DAY ( datetime_expr )
MONTH ( datetime_expr )
YEAR ( datetime_expr )

Examples:

DECLARE @now DATETIME2 = GETDATE();
SELECT @now                       --2016-07-21 15:05:33.8370000
SELECT DATEPART(YEAR, @now)       --2016
SELECT DATEPART(QUARTER, @now)    --3
SELECT DATEPART(WEEK, @now)       --30
SELECT DATEPART(HOUR, @now)       --15
SELECT DATEPART(MINUTE, @now)     --5
SELECT DATEPART(SECOND, @now)     --33
-- Differences between DATEPART and DATENAME:
SELECT DATEPART(MONTH, @now)      --7
SELECT DATENAME(MONTH, @now)      --July
SELECT DATEPART(WEEKDAY, @now)    --5
SELECT DATENAME(WEEKDAY, @now)    --Thursday
--shorthand functions
SELECT DAY(@now)    --21
SELECT MONTH(@now)  --7
SELECT YEAR(@now)   --2016

NOTE: DATEPART and DATENAME also accept abbreviations in the datepart parameter. Use of these abbreviations is generally discouraged as they can be confusing (m vs mi, ww vs w, etc.).

# Return just Date from a DateTime

There are many ways to return a Date from a DateTime object

  1. SELECT CONVERT(Date, GETDATE())
  2. SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) returns 2016-07-21 00:00:00.000
  3. SELECT CAST(GETDATE() AS DATE)
  4. SELECT CONVERT(CHAR(10),GETDATE(),111)
  5. SELECT FORMAT(GETDATE(), 'yyyy-MM-dd')

Note that options 4 and 5 returns a string, not a date.

# CROSS PLATFORM DATE OBJECT

In Transact SQL , you may define an object as Date (or DateTime) using the [DATEFROMPARTS][1] (or [DATETIMEFROMPARTS][1]) function like following:


DECLARE @myDate DATE=DATEFROMPARTS(1988,11,28) 
 DECLARE @someMoment DATETIME=DATEFROMPARTS(1988,11,28,10,30,50,123)

The parameters you provide are Year, Month, Day for the DATEFROMPARTS function and, for the DATETIMEFROMPARTS function you will need to provide year, month, day, hour, minutes, seconds and milliseconds.

These methods are useful and worth being used because using the plain string to build a date(or datetime) may fail depending on the host machine region, location or date format settings.

# Date parts reference

These are the datepart values available to date & time functions:

datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns

NOTE: Use of abbreviations is generally discouraged as they can be confusing (m vs mi, ww vs w, etc.). The long version of the datepart representation promotes clarity and readability, and should be used whenever possible (month, minute, week, weekday, etc.).

# Date Format Extended

Date Format SQL Statement Sample Output
YY-MM-DD SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 20), 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 11), '/', '-') AS [YY-MM-DD]
11-06-08
YYYY-MM-DD SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 111), '/', '-') AS [YYYY-MM-DD]
2011-06-08
YYYY-M-D SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M-D] 2011-6-8
YY-M-D SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M-D] 11-6-8
M-D-YYYY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-D-YYYY] 6-8-2011
M-D-YY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-D-YY] 6-8-11
D-M-YYYY SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D-M-YYYY] 8-6-2011
D-M-YY SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D-M-YY] 8-6-11
YY-MM SELECT RIGHT(CONVERT(VARCHAR(7), SYSDATETIME(), 20), 5) AS [YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), SYSDATETIME(), 120), 3, 5) AS [YY-MM]
11-06
YYYY-MM SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 120) AS [YYYY-MM] 2011-06
YY-M SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M] 11-6
YYYY-M SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M] 2011-6
MM-YY SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), SYSDATETIME(), 5), 4, 5) AS [MM-YY]
06-11
MM-YYYY SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 105), 7) AS [MM-YYYY] 06-2011
M-YY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-YY] 6-11
M-YYYY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-YYYY] 6-2011
MM-DD SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 10) AS [MM-DD] 06-08
DD-MM SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 5) AS [DD-MM] 08-06
M-D SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M-D] 6-8
D-M SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D-M] 8-6
M/D/YYYY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/D/YYYY] 6/8/2011
M/D/YY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/D/YY] 6/8/11
D/M/YYYY SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D/M/YYYY] 8/6/2011
D/M/YY SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D/M/YY] 8/6/11
YYYY/M/D SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M/D] 2011/6/8
YY/M/D SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M/D] 11/6/8
MM/YY SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 3), 5) AS [MM/YY] 06/11
MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 103), 7) AS [MM/YYYY] 06/2011
M/YY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/YY] 6/11
M/YYYY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/YYYY] 6/2011
YY/MM SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 11) AS [YY/MM] 11/06
YYYY/MM SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 111) AS [YYYY/MM] 2011/06
YY/M SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M] 11/6
YYYY/M SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M] 2011/6
MM/DD SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 1) AS [MM/DD] 06/08
DD/MM SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 3) AS [DD/MM] 08/06
M/D SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M/D] 6/8
D/M SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D/M] 8/6
MM.DD.YYYY SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), '/', '.') AS [MM.DD.YYYY] 06.08.2011
MM.DD.YY SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '.') AS [MM.DD.YY] 06.08.11
M.D.YYYY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.D.YYYY] 6.8.2011
M.D.YY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.D.YY] 6.8.11
DD.MM.YYYY SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY] 08.06.2011
DD.MM.YY SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 4) AS [DD.MM.YY] 08.06.11
D.M.YYYY SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D.M.YYYY] 8.6.2011
D.M.YY SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D.M.YY] 8.6.11
YYYY.M.D SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M.D] 2011.6.8
YY.M.D SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M.D] 11.6.8
MM.YYYY SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 104), 7) AS [MM.YYYY] 06.2011
MM.YY SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 4), 5) AS [MM.YY] 06.11
M.YYYY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.YYYY] 6.2011
M.YY SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.YY] 6.11
YYYY.MM SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 102) AS [YYYY.MM] 2011.06
YY.MM SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 2) AS [YY.MM] 11.06
YYYY.M SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M] 2011.6
YY.M SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M] 11.6
MM.DD SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 2), 5) AS [MM.DD] 06.08
DD.MM SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 4) AS [DD.MM] 08.06
MMDDYYYY SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), '/', '') AS [MMDDYYYY] 06082011
MMDDYY SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '') AS [MMDDYY] 060811
DDMMYYYY SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', '') AS [DDMMYYYY] 08062011
DDMMYY SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') AS [DDMMYY] 080611
MMYYYY SELECT RIGHT(REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', ''), 6) AS [MMYYYY] 062011
MMYY SELECT RIGHT(REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', ''), 4) AS [MMYY] 0611
YYYYMM SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 112) AS [YYYYMM] 201106
YYMM SELECT CONVERT(VARCHAR(4), SYSDATETIME(), 12) AS [YYMM] 1106
Month DD, YYYY SELECT DATENAME(MONTH, SYSDATETIME())+ ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ', ' + DATENAME(YEAR, SYSDATETIME()) AS [Month DD, YYYY] June 08, 2011
Mon YYYY SELECT LEFT(DATENAME(MONTH, SYSDATETIME()), 3) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Mon YYYY] Jun 2011
Month YYYY SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Month YYYY] June 2011
DD Month SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME()) AS [DD Month] 08 June
Month DD SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) AS [Month DD] June 08
DD Month YY SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ' ' + DATENAME(MM, SYSDATETIME()) + ' ' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [DD Month YY] 08 June 11
DD Month YYYY SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME())+ ' ' + DATENAME(YEAR, SYSDATETIME()) AS [DD Month YYYY] 08 June 2011
Mon-YY SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), SYSDATETIME(), 6), 6), ' ', '-') AS [Mon-YY] Jun-08
Mon-YYYY SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), SYSDATETIME(), 106), 8), ' ', '-') AS [Mon-YYYY] Jun-2011
DD-Mon-YY SELECT REPLACE(CONVERT(VARCHAR(9), SYSDATETIME(), 6), ' ', '-') AS [DD-Mon-YY] 08-Jun-11
DD-Mon-YYYY SELECT REPLACE(CONVERT(VARCHAR(11), SYSDATETIME(), 106), ' ', '-') AS [DD-Mon-YYYY] 08-Jun-2011

# Syntax

  • EOMONTH (start_date [, month_to_add ] )

# Remarks

as per https://msdn.microsoft.com/en-us/library/ms187819.aspx (opens new window), DateTimes are only precise to 3ms.

Rounding of datetime Fractional Second Precision datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

User-specified value System stored value
01/01/98 23:59:59.999 1998-01-02 00:00:00.000
--- ---
01/01/98 23:59:59.995 1998-01-01 23:59:59.997
01/01/98 23:59:59.996
01/01/98 23:59:59.997
01/01/98 23:59:59.998
--- ---
01/01/98 23:59:59.992 1998-01-01 23:59:59.993
01/01/98 23:59:59.993
01/01/98 23:59:59.994
--- ---
01/01/98 23:59:59.990 1998-01-01 23:59:59.990
01/01/98 23:59:59.991
--- ---

If more precision is required, time, datetime2 or datetimeoffset should be used.