How can i convert the datetime format below
2010-10-25 11:13:36.700
into
25-Oct-2010 or 2010-10-开发者_Go百科25 00:00:00.000
To get "25-Oct-2010"
Assuming the value is supplied as a string, not a DATETIME data type:
SELECT REPLACE(CONVERT(VARCHAR, CAST('2010-10-25 11:13:36.700' AS DATETIME), 106), ' ', '-')
See the CAST/CONVERT documentation for other formats, though the one you requested requires post-processing.
To get "2010-10-25 00:00:00.000"
The best performing means is to use DATEADD & DATEDIFF:
SELECT DATEADD(d, DATEDIFF(dd, 0, '2010-10-25 11:13:36.700'), 0)
References:
- DATEADD
- DATEDIFF
Testing
WITH sample AS (
SELECT CAST('2010-10-25 11:13:36.700' AS DATETIME) dt)
SELECT REPLACE(CONVERT(VARCHAR, s.dt, 106), ' ', '-') AS col1,
DATEADD(d, DATEDIFF(dd, 0, s.dt), 0) AS col2
FROM sample s
Returns:
col1 col2
-------------------------------------
25-Oct-2010 2010-10-25 00:00:00.000
Addendum
Being that you're on SQL Server 2005, you could make date formatting easier for yourself by creating a SQLCLR function that would allow you to use the .NET date formatting.
check this one
SELECT convert(VARCHAR, getdate(),106)
this will give you 25 Oct 2010
AND
SELECT REPLACE(convert(VARCHAR, getdate(),106), ' ' , '-')
this will give you 25-Oct-2010
Check if this can help you:
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-')
You can get more info Here
PD. Stackoverflow can be too addicted sometimes, you might wanna try google before
select CONVERT(NVARCHAR(20),getDATE(),105)
Please try
SELECT CONVERT(varchar, GETDATE(), 1) --12/30/06
SELECT CONVERT(varchar, GETDATE(), 2) --06.12.30
SELECT CONVERT(varchar, GETDATE(), 3) --30/12/06
SELECT CONVERT(varchar, GETDATE(), 4) --30.12.06
SELECT CONVERT(varchar, GETDATE(), 5) --30-12-06
SELECT CONVERT(varchar, GETDATE(), 6) --30 Dec 06
SELECT CONVERT(varchar, GETDATE(), 7) --Dec 30, 06
SELECT CONVERT(varchar, GETDATE(), 10) --12-30-06
SELECT CONVERT(varchar, GETDATE(), 11) --06/12/30
SELECT CONVERT(varchar, GETDATE(), 12) --061230
SELECT CONVERT(varchar, GETDATE(), 23) --2006-12-30
SELECT CONVERT(varchar, GETDATE(), 101) --12/30/2006
SELECT CONVERT(varchar, GETDATE(), 102) --2006.12.30
SELECT CONVERT(varchar, GETDATE(), 103) --30/12/2006
SELECT CONVERT(varchar, GETDATE(), 104) --30.12.2006
SELECT CONVERT(varchar, GETDATE(), 105) --30-12-2006
SELECT CONVERT(varchar, GETDATE(), 106) --30 Dec 2006
SELECT CONVERT(varchar, GETDATE(), 107) --Dec 30, 2006
SELECT CONVERT(varchar, GETDATE(), 110) --12-30-2006
SELECT CONVERT(varchar, GETDATE(), 111) --2006/12/30
SELECT CONVERT(varchar, GETDATE(), 112) --20061230
精彩评论