Obtain Start of Day from current_timestamp
Published: Jan 23, 2015
This is genuinely one of the things I’m most asked, and by all manner of people from DBAs to Devs because it’s never as simple as it looks and that is how to obtain the start of day from a current_timestamp (or any other datetime value).

As you would think, this is commonly used all over the place and in ways in which the newer DATE datatype is not appropriate. There are a multitude of ways to obtain the desired result and everyone has their favourite, so I thought I’d offer the one that I use the most.

Here’s the basic problem… we can get the current date and time in an easy function:

select current_timestamp


However, what happens if you want the date in question but with midnight as your time part? Suddenly there’s no easy method to achieve your goal.

As mentioned above, there are numerous methods, but my favourite is as follows:

select current_timestamp, convert(datetime, left(current_timestamp, 11))


It looks clumsy, and it mostly is, but I’m so used to typing it now that it’s the one I tend to go with.

One thing I will mention though, is not to be fooled into thinking “I use this everywhere… I’d like it in a function” because, as I’ve mentioned a LOT… scalar functions are evil. Therefore please don’t do this. If you must, then I’ve a solution below… but first… the main reason you shouldn’t…

Here I’ve grossly exaggerated a (nonsensical) query against my tweaked AdventureWorks database in order to force a parallel plan from the optimizer:

declare @i int = 1000

select top (@i) d.*, current_timestamp, convert(datetime, left(current_timestamp, 11))
from Sales.SalesOrderDetailEnlarged d
join sales.SalesOrderHeaderEnlarged h
on d.SalesOrderID = h.SalesOrderID
join sales.SalesOrderHeaderSalesReason r
on r.SalesOrderID = d.SalesOrderID
option (optimize for (@i = 2000000000))


All is good. So now let’s create our function as follows:

create function dbo.startOfDay(@datetime datetime)
returns datetime
as
begin
   return
convert(datetime, left(@datetime, 11))
end


Now we’ve got our helpful function to use in all our code, let’s apply it to the previous example:

declare @i int = 1000

select top (@i) d.*, current_timestamp, dbo.startOfDay(current_timestamp)
from Sales.SalesOrderDetailEnlarged d
join sales.SalesOrderHeaderEnlarged h
on d.SalesOrderID = h.SalesOrderID
join sales.SalesOrderHeaderSalesReason r
on r.SalesOrderID = d.SalesOrderID
option (optimize for (@i = 2000000000))


And just like that, we’re back in a land of serial plan slowness. NOT good.

To be honest you could write yourself a CLR to perform the same function (I haven’t actually got one of these, if I create one then I’ll post it here), or just write the code in your t-SQL and don’t use a function at all.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron