How to convert getdate to YYYY-MM-DD HH:MM:SS format with zero in hour minute and second

Posted on 2013-10-10
Medium Priority
Last Modified: 2013-10-11
How to convert getdate to YYYY-MM-DD HH:MM:SS format with zero in hour minute and second

Example '2013-10-10 00:00:00'
Question by:countrymeister
LVL 11

Expert Comment

by:Simone B
ID: 39564081
You can cast as DATE in SQL 2008:

select CAST(getdate() as DATE)

..which returns 2013-10-10

But if you really need the zeros, you can do this:

select cast(CAST(getdate() as DATE) as datetime)

..which returns 2013-10-10 00:00:00.000
LVL 34

Expert Comment

by:Brian Crowe
ID: 39564104
If you're using a pre-2008 compatibility level then you can try

LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39564109
select convert(varchar(21), getdate(), 120 )
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

LVL 35

Assisted Solution

by:David Todd
David Todd earned 500 total points
ID: 39564633

Assuming that fore a moment you need a character string, and not a datetime

select convert( varchar( 21 ), dateadd( day, datediff( day, 0, getdate()), 0 ), 120 )

LVL 49

Accepted Solution

PortletPaul earned 500 total points
ID: 39564813
No points please:

Most of the above is true, and these "round down" or cut-off getdate() to/at 12AM:

1.   cast(getdate() as date)
2a. dateadd(day, 0, datediff(day, 0, getdate()))
2b. cast(cast(getdate() as date) as datetime)
3.   convert( varchar( 21 ), dateadd( day, datediff( day, 0, getdate()), 0 ), 120 )

But what are you doing with it?

if comparing to date field use 1.

If you are comparing to a smalldate/datetime field, then I suggest 2a. or 2b
all SQL Server versions:
                           where A_DateTimeFld >= dateadd(day, 0, datediff(day, 0, getdate()))

in sql 2008+
                           where A_DateTimeFld >= cast(cast(getdate() as date) as datetime)

if a datetime2 field, cast to datetime2
                           where A_DateTimeFld >= cast(cast(getdate() as date) as datetime2)

3.  (or varchar variants) if comparing to smalldatetime/datetime/datetime2 fields these are slower than the above - but if only applying it to getdate() then that speed difference won't be noticeable. It does get noticeable if applied to rows data, which I assume isn't needed here.

If you need the output as part of a select clause, then you should use 3.

Author Closing Comment

ID: 39565289
Thank you Porter Paul, I was just not looking for a solution, but alos the reason behind the performance
LVL 49

Expert Comment

ID: 39565370
Thanks for the points - although I wasn't after any.

&, Well, for performance you want to avoid varchar manipulation whenever possible. The following chart shows results of some rudimentary tests I did recently converting a datetime field to just date with time at 12 AM (not that I recommend doing this, just for testing) on 1 million records.

The big surprise here is that I was expecting "dual casts" to be slower than a single cast - guess what? it wasn't, and in fact came in a surprising best.

I stress the testing isn't thorough you see it at: http://sqlfiddle.com/#!3/96f32/9
and the stats are just a manual record of multiple runs from the reports execution times.

round to date results
My belief is that:
 cast(cast(getdate() as date) as datetime)

comes in fastest because it avoids an implicit conversion from date to datetime (i.e. because we do it explicitly so a saving is made - but not sure where precisely)

I was also surprised this method is about 20% faster than the old stalwart dateadd method
dateadd(day, 0, datediff(day, 0, getdate()))

which has been subjected to testing before and generally wins - but not testing against the cast or dual cast methods - which is why I started the exercise.

Consistently, any varchar methods are way slower than using date functions, and this has been tested many times & not just by me.

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question