Calculate date parameters to use in program

Toad Data Point
1

Hi have sql program that I run each week which includes this statement:

where cast(a.createddate as date) >= cast(‘2015-04-05’ as date) and cast(a.createddate as date) <= cast(‘2015-04-11’ as date)

What I would like to do is calculate the start and end dates automatically without any hardcoding of dates.

This program is run every Monday for the previous week which commences Sunday and ends Saturday

So if I was to run it on Monday 13 April my start date would be previous Sunday minus 1 week - ‘2015-04-05’ and my end date would be previous Saturday - ‘2015-04-11’

Much appreciated if anyone can help me with the syntax to achieve this.

Sharon

2

Date functions vary by platform. Which one are you connecting to Oracle, Sql Server, Sybase, etc.? Relative date functions in Oracle just use sysdate - 7 and sysdate - 1. SQL Server use dateAdd(d, -7, getdate()) and dateAdd(d, -1, getdate()). Also instead of >= use where cast(a.createddate as date) between dateAdd(d, -7, getdate()) and dateAdd(d, -1, getdate()).

3

Thanks Greg for your response.

I am connecting to HP Vertica.

In theory I see why the above should work, however that isn’t suitable if it is run a day later, e.g. on a Tuesday.

When I ran the above statement I got this error:

ERROR 2624: Column “d” does not exist

I had tried the dateadd calculation before my posting but for some reason it thinks the datepart of d is a variable?

This is the statement to calculate the Saturday and Sunday but again, I got the error ‘Column “wk” does not exist’

SELECT DATEADD(wk, DATEDIFF(wk, -2, CURRENT_TIMESTAMP), -2) AS DAY_OF_WEEK /* Saturday */

SELECT DATEADD(wk, DATEDIFF(wk, -1, CURRENT_TIMESTAMP), -1) AS DAY_OF_WEEK /* Sunday */

I’ve tried the statements below but get this error:

ERROR 4856: Syntax error at or near “@” at character 9

DECLARE @startdate datetime

select @startdate = DATEADD(wk, DATEDIFF(wk, -1, CURRENT_TIMESTAMP), -1)

DECLARE @enddate datetime

select @enddate = DATEADD(wk, DATEDIFF(wk, -2, CURRENT_TIMESTAMP), -2)

Sharon

4

I don’t use HP vertica but it looks more like Oracle than SQL Server or Sybase. Try: between Next_Day(getdate(), ‘Saturday’) - 14 and Next_Day(getdate(), ‘Sunday’) - 7 . I am hoping it allows simple integer days add/subtract like Oracle but I have no idea. Good luck!

5

Thanks Greg that worked well with

select Next_Day(getdate(), ‘Sunday’) - 14 as start, Next_Day(getdate(), ‘Saturday’) - 7 as end

returning

5/04/2015 and 11/04/2015 respectively.

Thanks again for your help.