Sql Stored Procedure Date time

Question

in a sql server 2008 if a date time thats coming back is in the format of 12:00 AM 01/01/2012 1. How do i return or format 01/01/2012 in stored procedure only? 2. How do i return or format Jan 1 2012 in stored procedure only? 3. How do i return or format 12:00 AM in stored procedure only?

Akkiraju Ivaturi · Answer

Use this: Standard Date Formats Date Format Standard SQL Statement Sample Output Mon DD YYYY 1 HH:MIAM (or PM) Default SELECT CONVERT(VARCHAR(20) GETDATE() 100) Jan 1 2005 1:29PM 1 MM/DD/YY USA SELECT CONVERT(VARCHAR(8) GETDATE() 1) AS [MM/DD/YY] 11/23/98 MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10) GETDATE() 101) AS [MM/DD/YYYY] 11/23/1998 YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8) GETDATE() 2) AS [YY.MM.DD] 72.01.01 YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10) GETDATE() 102) AS [YYYY.MM.DD] 1972.01.01 DD/MM/YY British/French SELECT CONVERT(VARCHAR(8) GETDATE() 3) AS [DD/MM/YY] 19/02/72 DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10) GETDATE() 103) AS [DD/MM/YYYY] 19/02/1972 DD.MM.YY German SELECT CONVERT(VARCHAR(8) GETDATE() 4) AS [DD.MM.YY] 25.12.05 DD.MM.YYYY German SELECT CONVERT(VARCHAR(10) GETDATE() 104) AS [DD.MM.YYYY] 25.12.2005 DD-MM-YY Italian SELECT CONVERT(VARCHAR(8) GETDATE() 5) AS [DD-MM-YY] 24-01-98 DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10) GETDATE() 105) AS [DD-MM-YYYY] 24-01-1998 DD Mon YY 1 - SELECT CONVERT(VARCHAR(9) GETDATE() 6) AS [DD MON YY] 04 Jul 06 1 DD Mon YYYY 1 - SELECT CONVERT(VARCHAR(11) GETDATE() 106) AS [DD MON YYYY] 04 Jul 2006 1 Mon DD YY 1 - SELECT CONVERT(VARCHAR(10) GETDATE() 7) AS [Mon DD YY] Jan 24 98 1 Mon DD YYYY 1 - SELECT CONVERT(VARCHAR(12) GETDATE() 107) AS [Mon DD YYYY] Jan 24 1998 1 HH:MM:SS - SELECT CONVERT(VARCHAR(8) GETDATE() 108) 03:24:53 Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 Default + milliseconds SELECT CONVERT(VARCHAR(26) GETDATE() 109) Apr 28 2006 12:32:29:253PM 1 MM-DD-YY USA SELECT CONVERT(VARCHAR(8) GETDATE() 10) AS [MM-DD-YY] 01-01-06 MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10) GETDATE() 110) AS [MM-DD-YYYY] 01-01-2006 YY/MM/DD - SELECT CONVERT(VARCHAR(8) GETDATE() 11) AS [YY/MM/DD] 98/11/23 YYYY/MM/DD - SELECT CONVERT(VARCHAR(10) GETDATE() 111) AS [YYYY/MM/DD] 1998/11/23 YYMMDD ISO SELECT CONVERT(VARCHAR(6) GETDATE() 12) AS [YYMMDD] 980124 YYYYMMDD ISO SELECT CONVERT(VARCHAR(8) GETDATE() 112) AS [YYYYMMDD] 19980124 DD Mon YYYY HH:MM:SS:MMM(24h) 1 Europe default + milliseconds SELECT CONVERT(VARCHAR(24) GETDATE() 113) 28 Apr 2006 00:34:55:190 1 HH:MI:SS:MMM(24H) - SELECT CONVERT(VARCHAR(12) GETDATE() 114) AS [HH:MI:SS:MMM(24H)] 11:34:23:013 YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19) GETDATE() 120) 1972-01-01 13:42:24 YYYY-MM-DD HH:MI:SS.MMM(24h) ODBC Canonical (with milliseconds) SELECT CONVERT(VARCHAR(23) GETDATE() 121) 1972-02-19 06:35:24.489 YYYY-MM-DDTHH:MM:SS:MMM ISO8601 SELECT CONVERT(VARCHAR(23) GETDATE() 126) 1998-11-23T11:25:43:250 DD Mon YYYY HH:MI:SS:MMMAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26) GETDATE() 130) 28 Apr 2006 12:39:32:429AM 1 DD/MM/YYYY HH:MI:SS:MMMAM Kuwaiti SELECT CONVERT(VARCHAR(25) GETDATE() 131) 28/04/2006 12:39:32:429AM

David Smith · Answer

How would you do this for Access MDB instead of SQL Server. At the moment  I am doing a simple query below to Select Firstname LastName CreatedDateTime From ExampleTable

Akkiraju Ivaturi · Answer

Date Format SQL Statement Sample Output YY-MM-DD SELECT SUBSTRING(CONVERT(VARCHAR(10) GETDATE() 120) 3 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8) GETDATE() 11) '/' '-') AS [YY-MM-DD] 99-01-24 YYYY-MM-DD SELECT CONVERT(VARCHAR(10) GETDATE() 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10) GETDATE() 111) '/' '-') AS [YYYY-MM-DD] 1999-01-24 MM/YY SELECT RIGHT(CONVERT(VARCHAR(8) GETDATE() 3) 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8) GETDATE() 3) 4 5) AS [MM/YY] 08/99 MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10) GETDATE() 103) 7) AS [MM/YYYY] 12/2005 YY/MM SELECT CONVERT(VARCHAR(5) GETDATE() 11) AS [YY/MM] 99/08 YYYY/MM SELECT CONVERT(VARCHAR(7) GETDATE() 111) AS [YYYY/MM] 2005/12 Month DD YYYY 1 SELECT DATENAME(MM GETDATE()) + RIGHT(CONVERT(VARCHAR(12) GETDATE() 107) 9) AS [Month DD YYYY] July 04 2006 1 Mon YYYY 1 SELECT SUBSTRING(CONVERT(VARCHAR(11) GETDATE() 113) 4 8) AS [Mon YYYY] Apr 2006 1 Month YYYY 1 SELECT DATENAME(MM GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY] February 2006 1 DD Month 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM GETDATE()) AS [DD Month] 11 September 1 Month DD 1 SELECT DATENAME(MM GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD] September 11 1 DD Month YY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)) 2) AS [DD Month YY] 19 February 72 1 DD Month YYYY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY] 11 September 2002 1 MM-YY SELECT RIGHT(CONVERT(VARCHAR(8) GETDATE() 5) 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8) GETDATE() 5) 4 5) AS [MM-YY] 12/92 MM-YYYY SELECT RIGHT(CONVERT(VARCHAR(10) GETDATE() 105) 7) AS [MM-YYYY] 05-2006 YY-MM SELECT RIGHT(CONVERT(VARCHAR(7) GETDATE() 120) 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10) GETDATE() 120) 3 5) AS [YY-MM] 92/12 YYYY-MM SELECT CONVERT(VARCHAR(7) GETDATE() 120) AS [YYYY-MM] 2006-05 MMDDYY SELECT REPLACE(CONVERT(VARCHAR(10) GETDATE() 1) '/' '') AS [MMDDYY] 122506 MMDDYYYY SELECT REPLACE(CONVERT(VARCHAR(10) GETDATE() 101) '/' '') AS [MMDDYYYY] 12252006 DDMMYY SELECT REPLACE(CONVERT(VARCHAR(10) GETDATE() 3) '/' '') AS [DDMMYY] 240702 DDMMYYYY SELECT REPLACE(CONVERT(VARCHAR(10) GETDATE() 103) '/' '') AS [DDMMYYYY] 24072002 Mon-YY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9) GETDATE() 6) 6) ' ' '-') AS [Mon-YY] Sep-02 1 Mon-YYYY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11) GETDATE() 106) 8) ' ' '-') AS [Mon-YYYY] Sep-2002 1 DD-Mon-YY 1 SELECT REPLACE(CONVERT(VARCHAR(9) GETDATE() 6) ' ' '-') AS [DD-Mon-YY] 25-Dec-05 1 DD-Mon-YYYY 1 SELECT REPLACE(CONVERT(VARCHAR(11) GETDATE() 106) ' ' '-') AS [DD-Mon-YYYY] 25-Dec-2005 1

mohanad kayali · Answer

you can use these statements: SELECT GETDATE() AS UnconvertedDateTime CAST(GETDATE() AS nvarchar(30)) AS UsingCast CONVERT(nvarchar(30) GETDATE() 101) AS UsingConvertTo_ISO8601 ; GO