SQLSERVERLEARNER

How to convert datetime to date in sql server

This is a very frequent question by sql server database programmers.
The answer is vey simple, Use CAST.

The below query converts the date time value returned by getdate() into date.
[sql]
SELECT CAST(GETDATE() AS DATE)
[/sql]

The below query is using datetime value instead of getdate() function.
[sql]
SELECT CAST(‘2011-11-11 04:31:28.307′ AS DATE)
[/sql]

Following is more explaned piece of code:

[sql]
DECLARE @vDateTime DATETIME
DECLARE @vDate DATE
SET @vDateTime = GETDATE()
SET @vDate = CAST(@vDateTime as DATE)
SELECT @vDateTime DATETIME,@vDate DATE
[/sql]

The variable @vDateTime contains datetime and the variable @vdate contains the date extracted from the first variable.

Alternate ways to convert datetime to date:

Using CONVERT.

[sql]
SELECT CONVERT(DATE,GETDATE())
SELECT CONVERT(DATE,’2011-11-11 04:31:28.307′)
[/sql]

USING DATEADD AND DATEDIFF
[sql]
SELECT DATEADD(dd,0,DATEDIFF(dd,0,GETDATE()))
[/sql]

USING CONVERT AND varchar
[sql]
SELECT CONVERT(varchar(10),GETDATE(),102)
[/sql]

USING CAST AND INT
[sql]
SELECT cast(cast(( GETDATE() – 0.500000038580247) as int) as datetime)
[/sql]

USING CONVERT AND CHAR
[sql]
SELECT CONVERT(CHAR(10),GETDATE(),103)
[/sql]

USING CAST AND FLOOR
[sql]
SELECT CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime)
[/sql]

USING REPLACE AND CONVERT
[sql]
SELECT REPLACE(CONVERT(VARCHAR, GETDATE(),3),’/’,’-‘)
[/sql]

Use alternate ways given above for SQL SERVER 2000 and SQL SERVER 2005.
All the methods shown above work in SQL SERVER 2008.