How to convert datetime to date in sql server<\/p>\n
This is a very frequent question by sql server database programmers.
\nThe answer is vey simple, Use CAST.<\/p>\n
The below query converts the date time value returned by getdate() into date.
\n[sql]
\nSELECT CAST(GETDATE() AS DATE)
\n[\/sql]<\/p>\n
The below query is using datetime value instead of getdate() function.
\n[sql]
\nSELECT CAST(‘2011-11-11 04:31:28.307′ AS DATE)
\n[\/sql]<\/p>\n
Following is more explaned piece of code:<\/p>\n
[sql]
\nDECLARE @vDateTime DATETIME
\nDECLARE @vDate DATE
\nSET @vDateTime = GETDATE()
\nSET @vDate = CAST(@vDateTime as DATE)
\nSELECT @vDateTime DATETIME,@vDate DATE
\n[\/sql]<\/p>\n
The variable @vDateTime contains datetime and the variable @vdate contains the date extracted from the first variable.<\/p>\n
Alternate ways to convert datetime to date:<\/p>\n
Using CONVERT.<\/p>\n
[sql]
\nSELECT CONVERT(DATE,GETDATE())
\nSELECT CONVERT(DATE,’2011-11-11 04:31:28.307′)
\n[\/sql]<\/p>\n
USING DATEADD AND DATEDIFF
\n[sql]
\nSELECT DATEADD(dd,0,DATEDIFF(dd,0,GETDATE()))
\n[\/sql]<\/p>\n
USING CONVERT AND varchar
\n[sql]
\nSELECT CONVERT(varchar(10),GETDATE(),102)
\n[\/sql]<\/p>\n
USING CAST AND INT
\n[sql]
\nSELECT cast(cast(( GETDATE() – 0.500000038580247) as int) as datetime)
\n[\/sql]<\/p>\n
USING CONVERT AND CHAR
\n[sql]
\nSELECT CONVERT(CHAR(10),GETDATE(),103)
\n[\/sql]<\/p>\n
USING CAST AND FLOOR
\n[sql]
\nSELECT CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime)
\n[\/sql]<\/p>\n
USING REPLACE AND CONVERT
\n[sql]
\nSELECT REPLACE(CONVERT(VARCHAR, GETDATE(),3),’\/’,’-‘)
\n[\/sql]<\/p>\n
Use alternate ways given above for SQL SERVER 2000 and SQL SERVER 2005.
\nAll the methods shown above work in SQL SERVER 2008.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41],"tags":[294,386,387,295,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/79"}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=79"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/79\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=79"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=79"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=79"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}