Sql Stored Procedure Date time

David Smith

14y10.9k

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"?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

David Smith14y ago
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 Ivaturi14y ago

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 ¹	SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]	July 04, 2006¹
Mon YYYY¹	SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]	Apr 2006 ¹
Month YYYY ¹	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]	February 2006 ¹
DD Month¹	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]	11 September ¹
Month DD¹	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]	September 11 ¹
DD Month YY ¹	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 ¹
DD Month YYYY ¹	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]	11 September 2002 ¹
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 ¹	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]	Sep-02 ¹
Mon-YYYY¹	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]	Sep-2002 ¹
DD-Mon-YY ¹	SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]	25-Dec-05 ¹
DD-Mon-YYYY ¹	SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]	25-Dec-2005¹

Akkiraju Ivaturi14y ago

Use this:

Standard Date Formats
Date Format	Standard	SQL Statement	Sample Output
Mon DD YYYY ¹ HH:MIAM (or PM)	Default	SELECT CONVERT(VARCHAR(20), GETDATE(), 100)	Jan 1 2005 1:29PM ¹
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 ¹	-	SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]	04 Jul 06 ¹
DD Mon YYYY ¹	-	SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]	04 Jul 2006 ¹
Mon DD, YY ¹	-	SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]	Jan 24, 98 ¹
Mon DD, YYYY ¹	-	SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]	Jan 24, 1998 ¹
HH:MM:SS	-	SELECT CONVERT(VARCHAR(8), GETDATE(), 108)	03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) ¹	Default + milliseconds	SELECT CONVERT(VARCHAR(26), GETDATE(), 109)	Apr 28 2006 12:32:29:253PM ¹
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) ¹	Europe default + milliseconds	SELECT CONVERT(VARCHAR(24), GETDATE(), 113)	28 Apr 2006 00:34:55:190 ¹
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 ¹	Kuwaiti	SELECT CONVERT(VARCHAR(26), GETDATE(), 130)	28 Apr 2006 12:39:32:429AM ¹
DD/MM/YYYY HH:MI:SS:MMMAM	Kuwaiti	SELECT CONVERT(VARCHAR(25), GETDATE(), 131)	28/04/2006 12:39:32:429AM

mohanad kayali14y ago
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