Convert date and time to sql server

Question

How can I convert the datetime format below

2010-10-25 11:13: 36.700

at

October 25, 2010 or 2010-10-25 00: 00: 00.000

Answer 1

To get October 25th, 2010

Assuming the value is provided as a string and 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, although what you request requires further processing.

To get the "2010-10-25 00: 00: 00.000"

The best way is to use DATEADD and DATEDIFF:

SELECT DATEADD(d, DATEDIFF(dd, 0, '2010-10-25 11:13:36.700'), 0)

Literature:

• 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

:

col1          col2
-------------------------------------
25-Oct-2010   2010-10-25 00:00:00.000

SQL Server 2005, , SQLCLR, .NET.

Answer 2

SELECT convert(VARCHAR, getdate(),106)

25 Oct 2010

SELECT REPLACE(convert(VARCHAR, getdate(),106), ' ' , '-')

25-Oct-2010

Answer 3

Check if this can help you:

SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') 

You can get more information here.

PD. Sometimes stackoverflow may be too addictive, you can try google before

Answer 4

select CONVERT (NVARCHAR (20), getDATE (), 105)