sql  GETDATE()

niceguy971
niceguy971 used Ask the Experts™
on
select GETDATE() returns   2012-05-23 22:40:51.237

I want that date  without the time part : 2012-05-23 00:00:00.000

I can use SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) to get date  without the time part..
What would be the best  way to do it??

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you want to return a string, you can concatenation year(getdate()) +"-"+month(getdate())+"-"+day(getdate())
Sorry, should have read this closer. Try cast or convert to "date" type
http://msdn.microsoft.com/en-us/library/bb630352.aspx
You can also use:
SELECT CONVERT(DATE, GETDATE())

Open in new window


If you need just the date but as a datetime, you can use:
SELECT CONVERT(DATETIME, CONVERT(DATE, GETDATE()))

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Try this

select convert(nvarchar,getdate(),103) date

Open in new window


101   U.S.                       mm/dd/yyyy
102   ANSI                      yy.mm.dd
103   British/French   dd/mm/yyyy
104   German               dd.mm.yy
105   Italian                   dd-mm-yy
select cast(floor(cast(getdate() as float)) as datetime)
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
The most efficient way to strip the time is similar to what you originally had:

SELECT DATEADD(DAY, DATEDIFF(dd, 0, GETDATE()), 0)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial