SQL Server – Dates in Different Formats

Date in Different Formats

Dates are very frequently used and saved in the Database tables. Saving date using DATETIME datatype is very simple. But it gets really tricky when we want to convert that date and use it in different formats as per our requirements.

The CONVERT command comes to our rescue over here. Following is the syntax for CONVERT command.

CONVERT (length_of_output, date, format_code)

Use of CONVERT command to convert Current Date into CHAR datatype and display it in different formats using format_code is show below:

SELECT CONVERT(CHAR(100), GETDATE(), 0 ) AS YouGotDate — Displays — Aug 19 2009 6:46AM
SELECT CONVERT(CHAR(100), GETDATE(), 1 ) AS YouGotDate — Displays — 08/19/09
SELECT CONVERT(CHAR(100), GETDATE(), 2 ) AS YouGotDate — Displays — 09.08.19
SELECT CONVERT(CHAR(100), GETDATE(), 3 ) AS YouGotDate — Displays — 19/08/09
SELECT CONVERT(CHAR(100), GETDATE(), 4 ) AS YouGotDate — Displays — 19.08.09
SELECT CONVERT(CHAR(100), GETDATE(), 5 ) AS YouGotDate — Displays — 19-08-09
SELECT CONVERT(CHAR(100), GETDATE(), 6 ) AS YouGotDate — Displays — 19 Aug 09
SELECT CONVERT(CHAR(100), GETDATE(), 7 ) AS YouGotDate — Displays — Aug 19, 09
SELECT CONVERT(CHAR(100), GETDATE(), 8 ) AS YouGotDate — Displays — 06:46:45
SELECT CONVERT(CHAR(100), GETDATE(), 9 ) AS YouGotDate — Displays — Aug 19 2009 6:46:45:507AM
SELECT CONVERT(CHAR(100), GETDATE(), 10 ) AS YouGotDate — Displays — 08-19-09
SELECT CONVERT(CHAR(100), GETDATE(), 11 ) AS YouGotDate — Displays — 09/08/19
SELECT CONVERT(CHAR(100), GETDATE(), 12 ) AS YouGotDate — Displays — 090819
SELECT CONVERT(CHAR(100), GETDATE(), 13 ) AS YouGotDate — Displays — 19 Aug 2009 06:46:45:507
SELECT CONVERT(CHAR(100), GETDATE(), 14 ) AS YouGotDate — Displays — 06:46:45:507
SELECT CONVERT(CHAR(100), GETDATE(), 20 ) AS YouGotDate — Displays — 2009-08-19 06:46:45
SELECT CONVERT(CHAR(100), GETDATE(), 21 ) AS YouGotDate — Displays — 2009-08-19 06:46:45.540
SELECT CONVERT(CHAR(100), GETDATE(), 22 ) AS YouGotDate — Displays — 08/19/09 6:46:45 AM
SELECT CONVERT(CHAR(100), GETDATE(), 23 ) AS YouGotDate — Displays — 2009-08-19
SELECT CONVERT(CHAR(100), GETDATE(), 24 ) AS YouGotDate — Displays — 06:46:45
SELECT CONVERT(CHAR(100), GETDATE(), 25 ) AS YouGotDate — Displays — 2009-08-19 06:46:45.540
SELECT CONVERT(CHAR(100), GETDATE(), 100 ) AS YouGotDate — Displays — Aug 19 2009 6:46AM
SELECT CONVERT(CHAR(100), GETDATE(), 101 ) AS YouGotDate — Displays — 08/19/2009
SELECT CONVERT(CHAR(100), GETDATE(), 102 ) AS YouGotDate — Displays — 2009.08.19
SELECT CONVERT(CHAR(100), GETDATE(), 103 ) AS YouGotDate — Displays — 19/08/2009
SELECT CONVERT(CHAR(100), GETDATE(), 104 ) AS YouGotDate — Displays — 19.08.2009
SELECT CONVERT(CHAR(100), GETDATE(), 105 ) AS YouGotDate — Displays — 19-08-2009
SELECT CONVERT(CHAR(100), GETDATE(), 106 ) AS YouGotDate — Displays — 19 Aug 2009
SELECT CONVERT(CHAR(100), GETDATE(), 107 ) AS YouGotDate — Displays — Aug 19, 2009
SELECT CONVERT(CHAR(100), GETDATE(), 108 ) AS YouGotDate — Displays — 06:46:45
SELECT CONVERT(CHAR(100), GETDATE(), 109 ) AS YouGotDate — Displays — Aug 19 2009 6:46:45:913AM
SELECT CONVERT(CHAR(100), GETDATE(), 110 ) AS YouGotDate — Displays — 08-19-2009
SELECT CONVERT(CHAR(100), GETDATE(), 111 ) AS YouGotDate — Displays — 2009/08/19
SELECT CONVERT(CHAR(100), GETDATE(), 112 ) AS YouGotDate — Displays — 20090819
SELECT CONVERT(CHAR(100), GETDATE(), 113 ) AS YouGotDate — Displays — 19 Aug 2009 06:46:45:930
SELECT CONVERT(CHAR(100), GETDATE(), 114 ) AS YouGotDate — Displays — 06:46:45:930
SELECT CONVERT(CHAR(100), GETDATE(), 120 ) AS YouGotDate — Displays — 2009-08-19 06:46:45
SELECT CONVERT(CHAR(100), GETDATE(), 121 ) AS YouGotDate — Displays — 2009-08-19 06:46:45.943
SELECT CONVERT(CHAR(100), GETDATE(), 126 ) AS YouGotDate — Displays — 2009-08-19T06:46:45.990
SELECT CONVERT(CHAR(100), GETDATE(), 130 ) AS YouGotDate — Displays — 15 ????? 1419 6:46:46:040AM
SELECT CONVERT(CHAR(100), GETDATE(), 131 ) AS YouGotDate — Displays — 15/08/1419 6:46:46:040AM
SELECT CONVERT(CHAR(100), GETDATE(), 256 ) AS YouGotDate — Displays — Aug 19 2009 6:46AM

Spoilt for choice baby! Correct.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: