Using Relative Dates

[robertfah]

I'm writing a program that creates simple SQL based on Relative dates instead of real dates. So a user can pick an operator (<, >, =) and then choose a relative date:

Yesterday
Today
Tomorrow
ThisWeek
LastWeek
ThisMonth
LastMonth
ThisYear
LastYear

and the code would spit out something like this:

SELECT i.DateCreated
FROM Items i 
WHERE 1=1
AND i.DateCreated < getdate()

Does anyone know I can calculate the relative dates? for instance, if they pick Tomorrow, then how would I use getdate() to make it tomorrow? and so on for the rest of the relative dates?

Thanks in advance!

 

[robertfah]

I figured out a few of them, but does anyone know how I can get the last 3 (the ones with the ???)?

SELECT 'Today', getdate()
SELECT 'Tomorrow', DATEADD(day, 1, getdate())
SELECT 'Yesterday', DATEADD(day, -1, getdate())
SELECT 'LastWeek', DATEADD(day, -7, getdate())
SELECT 'LastMonth', DATEADD(Month, -1, getdate())
SELECT 'LastYear', DATEADD(Year, -1, getdate())
SELECT 'ThisWeek', '???'
SELECT 'ThisMonth', '???'
SELECT 'ThisYear', '???'

 

[RiverGuy]

Adding a Date table to your database comes in really handy. For every date, you will have columns to store the year, month, quarter, week, etc. Anyways, if you don't want to go that route, you can use something like the following:

--Start of Week
SELECT DATEADD(day, DATEPART(dw, GETDATE()) * -1, GETDATE())

--End of Week
SELECT DATEADD(day, 7 - DATEPART(dw, GETDATE()) , GETDATE())

--Start of Month
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GETDATE())) + RIGHT('0' + CONVERT(VARCHAR(2), MONTH(GETDATE())),2) + '01')

--End of Month
SELECT DATEADD(day, -1, DATEADD(month, 1, CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GETDATE())) + RIGHT('0' + CONVERT(VARCHAR(2), MONTH(GETDATE())),2) + '01')))

--Start of Year
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GETDATE())) + '0101')

--End of Year
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4), YEAR(GETDATE())) + '1231')

Be sure you understand how DATEFIRST works when working with days of the week. Also, depending on your situation, you might need to strip off the time portions of your date when using GETDATE().

 

[mutley1]

Or just to be a killjoy, could you not give them 2 parameters - startdate and enddate and then use those to do a

where datecreated >= @startdate 
and datecreated <= @enddate

?

Cheers,

M.

 

[RyanEK]

Hi,

You can use the following:

// This week. Note depending on what set for @@datefirst this could be Monday or Sunday
select DATEADD(dd, 1 - DATEPART(dw, getdate()), getdate())

// This Month
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

// This Year
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

Ryan