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.
‘ how to convert datetime to date in sql’ thread was really handy search. thanks for the post