Convert datetime in sql server

Question

How can i convert the datetime format below

2010-10-25 11:13:36.700

into

25-Oct-2010 or 2010-10-25 00:00:00.000

Answer 1

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.

Answer 2

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