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

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'
LVL 1
countrymeisterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Simone BSenior E-Commerce AnalystCommented:
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
0
Brian CroweDatabase AdministratorCommented:
If you're using a pre-2008 compatibility level then you can try

SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))
0
Aneesh RetnakaranDatabase AdministratorCommented:
or
select convert(varchar(21), getdate(), 120 )
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

David ToddSenior DBACommented:
Hi,

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 )

HTH
  David
0
PortletPaulfreelancerCommented:
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
e.g.
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
countrymeisterAuthor Commented:
Thank you Porter Paul, I was just not looking for a solution, but alos the reason behind the performance
0
PortletPaulfreelancerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.