You may want to convert the date into different format that you want. At here, I got example how to convert date to 15 different formats using SQL Query.--'YYYYMMDD'
SELECT CONVERT(CHAR(8), GETDATE(), 112)
--'YYYY-MM-DD'
SELECT CONVERT(CHAR(10), GETDATE(), 23)
--'YYYY-MMM-DD'
SELECT STUFF(CONVERT(CHAR(10), GETDATE(), 23), 6, 2, LEFT(DATENAME(m, GETDATE()), 3))
--'YYMMDD'
SELECT CONVERT(VARCHAR(8), GETDATE(), 12)
--'YY-MM-DD'
SELECT STUFF(STUFF(CONVERT(VARCHAR(8), GETDATE(), 12), 5, 0, '-'), 3, 0, '-')
--'YY-MMM-DD'
SELECT STUFF(STUFF(STUFF(CONVERT(VARCHAR(8), GETDATE(), 12), 3, 2, LEFT(DATENAME(m, GETDATE()), 3)), 6, 0, '-'), 3, 0, '-')
--'MM-DD-YY'
SELECT CONVERT(CHAR(8), GETDATE(), 10)
--'MMDDYY'
SELECT REPLACE(CONVERT(CHAR(8), GETDATE(), 10), '-', SPACE(0))
--'MM/DD/YY'
SELECT CONVERT(CHAR(8), GETDATE(), 1)
--'MM/DD/YYYY'
SELECT CONVERT(CHAR(10), GETDATE(), 101)
--'DD-MM-YY'
SELECT REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', '-')
--'DD-MMM-YY'
SELECT STUFF(REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', '-'), 4, 2, LEFT(DATENAME(m, GETDATE()), 3))
--'DDMMYY'
SELECT REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', SPACE(0))
--'DD/MM/YY'
SELECT CONVERT(CHAR(8), GETDATE(), 3)
--'DD/MM/YYYY'
SELECT CONVERT(CHAR(10), GETDATE(), 103)
Hope that is one the format you want. If not, please write a comment with the format date you want. I will try my best to help you. Thanks.
Tuesday, March 11, 2008
Ways to Convert Date to Different Formats in SQL Server 2000
Subscribe to:
Post Comments (Atom)
2 comments:
Hi. I need an urgent help. Imagine I have thousands of rows in my table with Date column with a data type : VARCHAR (--/--/----). The current statement I my webapp have is as follows:
SELECT
COUNT(*)AS ICT
FROM jack_LeadTimes
WHERE MDATE = '#today#'
Now I need to implement "Convert Function" in to it so I get back from DB a datetime datatype in the very same layout. Please, help out!
Let say the name of date column in VARCHAR type is DateA and store date in varchar in format 'dd/MM/yyyy'. You can do like below:
SELECT
SELECT CONVERT(DATETIME, DateA, 103)
FROM jack_LeadTimes
WHERE MDATE = '#today#'
Hope this help you.
Post a Comment