SQL Get Start of Day

SQL Get Start of Day

As many of you know SQL can be fun when you are dealing with records in the database.  One common thing that I have always been required to do is to get Records where their date is greater than the start of the day.

Imagine this Scenario.

DateLogged = '2012-10-08 00:00:00.000'

If I run the following command

Select GetDate()

This Returns: '2012-10-08 12:59:11.887' 

So how can I go about building a query that will get me this date?

Simple!, We basically need to tell SQL to get us the first second of the day or earlier.

select dateadd(second,0,dateadd(day,datediff(day,0,getdate()),0)

This Returns: '2012-10-08 00:00:00.000'

Start of Day x Number of Days Ago Excluding Weekends

Now the next problem I have had is getting a record that was added say 3 days ago but I want SQL to Ignore the weekend. 

Imagine this scenario

DateLogged = '2012-10-05 00.00.00:000'

To get this date I can achieve this by running the following command

select dateadd(weekday, -3, GetDate())

This Returns: '2012-10-05 12:59:11.887'

However this is not what I want, so if we adapt the sql above.

select dateadd(second,0,dateadd(weekday,-3,dateadd(day,datediff(day,0,getdate()),0)))

This Returns: '2012-10-05 00:00:00.000'

Obviously I do not believe this is the most efficient way of doing this but it is rather dynamic.