Summary:
From this post you will learn how to use date and time conversion function.
From this post you will learn how to use date and time conversion function.
SQL Server provides a number of options you can use to format a date/time string.
How to get different SQL Server date formats:
- Use the date format option along with CONVERT function.
- To get YYYY-MM-DD use SELECT CONVERT(varchar, getdate(), 23).
- To get MM/DD/YYYY use SELECT CONVERT(varchar, getdate(), 1).
- Check out the chart to get a list of all format options.
Convert datetime to date using the CONVERT() function
This statement uses the CONVERT() function to convert a datetime to a date:
Syntax:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
expression - Any valid expression.
data_type - The target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used.
length - An optional integer that specifies the length of the target data type, for data types that allow a user specified length. The default value is 30.
style - An integer expression that specifies how the CONVERT function will translate expression. For a style value of NULL, NULL is returned. data_type determines the range.
Below is a list of formats and an example of the output. The date used for all of these examples is "2006-12-30 00:38:54.840".
Date Only Formats
Format # : 1
Query: select convert(varchar, getdate(), 1)
Sample: 12/30/06
Format # : 2
Query: select convert(varchar, getdate(), 2)
Sample: 06.12.30
Format # : 3
Query: select convert(varchar, getdate(), 3)
Sample: 30/12/06
Format # : 4
Query: select convert(varchar, getdate(), 4)
Sample: 30.12.06
Format # : 5
Query: select convert(varchar, getdate(), 5)
Sample: 30-12-06
Format # : 6
Query: select convert(varchar, getdate(), 6)
Sample: 30 Dec 06
Format # : 7
Query: select convert(varchar, getdate(), 7)
Sample: Dec 30, 06
Format # : 10
Query: select convert(varchar, getdate(), 10)
Sample: 12-30-06
Format # : 11
Query: select convert(varchar, getdate(), 11)
Sample: 06/12/30
Format # : 12
Query: select convert(varchar, getdate(), 12)
Sample: 061230
Format # : 23
Query: select convert(varchar, getdate(), 23)
Sample: 2006-12-30
Format # : 101
Query: select convert(varchar, getdate(), 101)
Sample: 12/30/2006
Format # : 102
Query: select convert(varchar, getdate(), 102)
Sample: 2006.12.30
Format # : 103
Query: select convert(varchar, getdate(), 103)
Sample: 30/12/2006
Format # : 104
Query: select convert(varchar, getdate(), 104)
Sample: 30.12.2006
Format # : 105
Query: select convert(varchar, getdate(), 105)
Sample: 30-12-2006
Format # : 106
Query: select convert(varchar, getdate(), 106)
Sample: 30 Dec 2006
Format # : 107
Query: select convert(varchar, getdate(), 107)
Sample: Dec 30, 2006
Format # : 110
Query: select convert(varchar, getdate(), 110)
Sample: 12-30-2006
Format # : 111
Query: select convert(varchar, getdate(), 111)
Sample: 2006/12/30
Format # : 112
Query: select convert(varchar, getdate(), 112)
Sample: 20061230
Time Only Formats
Format # : 8
Query: select convert(varchar, getdate(), 8)
Sample: 00:38:54
Format # : 14
Query: select convert(varchar, getdate(), 14)
Sample: 00:38:54:840
Format # : 24
Query: select convert(varchar, getdate(), 24)
Sample: 00:38:54
Format # : 108
Query: select convert(varchar, getdate(), 108)
Sample: 00:38:54
Format # : 114
Query: select convert(varchar, getdate(), 114)
Sample: 00:38:54:840
Date & Time Formats
Format # : 0
Query: select convert(varchar, getdate(), 0)
Sample: Dec 12 2006 12:38AM
Format # : 9
Query: select convert(varchar, getdate(), 9)
Sample: Dec 30 2006 12:38:54:840AM
Format # : 13
Query: select convert(varchar, getdate(), 13)
Sample: 30 Dec 2006 00:38:54:840AM
Format # : 20
Query: select convert(varchar, getdate(), 20)
Sample: 2006-12-30 00:38:54
Format # : 21
Query: select convert(varchar, getdate(), 21)
Sample: 2006-12-30 00:38:54.840
Format # : 22
Query: select convert(varchar, getdate(), 22)
Sample: 12/30/06 12:38:54 AM
Format # : 25
Query: select convert(varchar, getdate(), 25)
Sample: 2006-12-30 00:38:54.840
Format # : 100
Query: select convert(varchar, getdate(), 100)
Sample: Dec 30 2006 12:38AM
Format # : 109
Query: select convert(varchar, getdate(), 109)
Sample: Dec 30 2006 12:38:54:840AM
Format # : 113
Query: select convert(varchar, getdate(), 113)
Sample: 30 Dec 2006 00:38:54:840
Format # : 120
Query: select convert(varchar, getdate(), 120)
Sample: 2006-12-30 00:38:54
Format # : 121
Query: select convert(varchar, getdate(), 121)
Sample: 2006-12-30 00:38:54.840
Format # : 126
Query: select convert(varchar, getdate(), 126)
Sample: 2006-12-30T00:38:54.840
Format # : 127
Query: select convert(varchar, getdate(), 127)
Sample: 2006-12-30T00:38:54.840