Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.