convert function (Date conversion)
We have used convert function for conversion of data type.
Numeric or a date to a string, the number or date must first
be cast to a string. The CONVERT function has an optional parameter called style
that can be used to format a date.
We have many time used cast to format the date in various
style.
SELECT
CAST(DATEPART(YYYY,GETDATE()) AS VARCHAR) + '/' +
CAST(DATEPART(MM,GETDATE())
AS VARCHAR) +
'/' + CAST(DATEPART(DD,GETDATE()) AS VARCHAR) AS fnDateCast;
We can achive
this by accomplishing convert in our sql statements.
Syntax:-
CONVERT(<data type, usually
varchar>,<date>,<style>)
DECLARE @TODAY DATETIME=GETDATE();
SELECT CONVERT(VARCHAR,@TODAY,1) AS "1",
CONVERT(VARCHAR,@TODAY,101) AS "101",
CONVERT(VARCHAR,@TODAY,2) AS
"2",
CONVERT(VARCHAR,@TODAY,102) AS "102",
CONVERT(VARCHAR(10),GETDATE(),10) AS "10",
CONVERT(VARCHAR(10),GETDATE(),110) AS "110" ,
CONVERT(VARCHAR(11),GETDATE(),6) AS "6" ,
CONVERT(VARCHAR(11),GETDATE(),106)AS "106" ,
CONVERT(VARCHAR(24),GETDATE(),113) AS "113";
|
Standard Date conversion in different
Formats
|
|||
|
Date Format
|
Standard
|
SQL Statement
|
Sample Output
|
|
Mon DD YYYY 1
HH:MIAM (or PM) |
Default
|
SELECT
CONVERT(VARCHAR(20), SYSDATETIME(), 100)
|
Jun 8 2011 1:30PM 1
|
|
MM/DD/YY
|
USA
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY]
|
06/08/11
|
|
MM/DD/YYYY
|
USA
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY]
|
06/08/2011
|
|
YY.MM.DD
|
ANSI
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD]
|
11.06.08
|
|
YYYY.MM.DD
|
ANSI
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD]
|
2011.06.08
|
|
DD/MM/YY
|
British/French
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY]
|
08/06/11
|
|
DD/MM/YYYY
|
British/French
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY]
|
08/06/2011
|
|
DD.MM.YY
|
German
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY]
|
08.06.11
|
|
DD.MM.YYYY
|
German
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]
|
08.06.2011
|
|
DD-MM-YY
|
Italian
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY]
|
08-06-11
|
|
DD-MM-YYYY
|
Italian
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 105) AS [DD-MM-YYYY]
|
08-06-2011
|
|
DD Mon YY 1
|
-
|
SELECT
CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY]
|
08 Jun 11 1
|
|
DD Mon YYYY 1
|
-
|
SELECT
CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY]
|
08 Jun 2011 1
|
|
Mon DD, YY 1
|
-
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY]
|
Jun 08, 11 1
|
|
Mon DD, YYYY 1
|
-
|
SELECT
CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY]
|
Jun 08, 2011 1
|
|
HH:MM:SS
|
-
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 8)
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 108) |
13:30:45
|
|
Mon D YYYY H:MI:SS.NNNNNNNAM (or PM) 1
|
Default +
nanoseconds |
SELECT
CONVERT(VARCHAR(26), SYSDATETIME(), 9)
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109) |
Jun 8 2011 1:30:45.9428675PM 1
|
|
MM-DD-YY
|
USA
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY]
|
06-08-11
|
|
MM-DD-YYYY
|
USA
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY]
|
06-08-2011
|
|
YY/MM/DD
|
-
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD]
|
11/06/08
|
|
YYYY/MM/DD
|
-
|
SELECT
CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD]
|
2011/06/08
|
|
YYMMDD
|
ISO
|
SELECT
CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD]
|
110608
|
|
YYYYMMDD
|
ISO
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD]
|
20110608
|
|
DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1
|
Europe default + nanoseconds
|
SELECT
CONVERT(VARCHAR(30), SYSDATETIME(), 13)
SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 113) |
08 Jun 2011 13:30:45.94286751
|
|
HH:MI:SS.NNNNNNN(24H)
|
-
|
SELECT
CONVERT(VARCHAR(16), SYSDATETIME(), 14) AS [HH:MI:SS:MMM(24H)]
SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 114) AS [HH:MI:SS:MMM(24H)] |
13:30:45.9428675
|
|
YYYY-MM-DD HH:MI:SS(24h)
|
ODBC Canonical
|
SELECT
CONVERT(VARCHAR(19), SYSDATETIME(), 120)
|
2011-06-08 13:30:45
|
|
YYYY-MM-DD HH:MI:SS.NNNNNNN(24h)
|
ODBC Canonical
(with nanoseconds) |
SELECT
CONVERT(VARCHAR(23), SYSDATETIME(), 121)
|
2011-06-08 13:30:45.9428675
|
|
MM/DD/YY HH:MI:SS AM
|
-
|
SELECT
CONVERT(VARCHAR(20), SYSDATETIME(), 22)
|
06/08/11 1:30:45 PM
|
|
YYYY-MM-DD
|
-
|
SELECT
CONVERT(VARCHAR(26), SYSDATETIME(), 23)
|
2011-06-091
|
|
HH:MI:SS (24h)
|
-
|
SELECT
CONVERT(VARCHAR(8), SYSDATETIME(), 24)
|
13:30:45
|
|
YYYY-MM-DD HH:MI:SS.NNNNNNN
|
-
|
SELECT
CONVERT(VARCHAR(26), SYSDATETIME(), 25)
|
2011-06-08 13:30:45.94286751
|
|
YYYY-MM-DDTHH:MM:SS:NNNNNNN
|
ISO8601
|
SELECT
CONVERT(VARCHAR(27), SYSDATETIME(), 126)
|
2011-06-08T13:30:45.9428675
|
|
DD Mon YYYY HH:MI:SS.NNNNNNNAM 1
|
Kuwaiti
|
SELECT
CONVERT(VARCHAR(26), SYSDATETIME(), 130)
|
08 Jun 2011 1:30:45.9428675PM1
|
|
DD/MM/YYYY HH:MI:SS.NNNNNNNAM
|
Kuwaiti
|
SELECT
CONVERT(VARCHAR(25), SYSDATETIME(), 131)
|
08/06/2011 1:30:45.9428675PM
|
No comments:
Post a Comment