1

Question:

I have a query to select rows dated "last Friday from a given date", using the dateadd/datepart/modulo 7 trick. It works fine in Sybase ASE, but returns 0 rows in Sybase IQ. If I hard code the date into the query, it works in IQ as well, but I need the query to be dynamic.

Sample data:

create table #tmp1 (test_date date)
insert into #tmp1 values ('2017-09-08') -- Sep 08 is a Friday

Expanded query that works in ASE but not in IQ
Note the date provided is Tue Sep 12, and it finds "last Friday" from that date, which should be Sep 08.

select * from #tmp1 
where test_date = 
    convert(
        date,
        dateadd(day, -((datepart(cdw, '2017-09-12') + 2) % 7),
        '2017-09-12')
    )

The gist of the query is that it will take the given date (Tue Sep 12) and find out how many days it has been since Friday. It then subtracts that many days from the given date, and uses that date in the where clause.

I expect the query to return the row in the temp table, and it works in ASE, but returns 0 rows in IQ. Can anybody explain why? If I hard code the Friday date, it finds the row just fine.

Edit: I've found that assigning the "last Friday" date to a variable, and then querying with that variable does work. Details below. However, using a variable isn't an option in this case, and I'm still very confused about why "placing the calculated date into a variable, then using the variable" works, but "calculating the date directly in the where clause" does not.


Some debugging samples when querying #tmp1 in IQ:

Actual query that fails in IQ (it returns the row correctly in ASE):

select * from #tmp1 where test_date = convert(date, dateadd(day, -((datepart(cdw, '2017-09-12') + 2) % 7), '2017-09-12'))

0 rows

Hardcoded date:

select * from #tmp1 where test_date='2017-09-08'

9/8/2017


Deconstructing the query, to make sure the values are correct:

How many days are subtracted via the dateadd:

select ((datepart(cdw, '2017-09-12') + 2) % 7)

4

What date is calculated by the dateadd:

select dateadd(day, -((datepart(cdw, '2017-09-12') + 2) % 7), '2017-09-12')

9/8/2017 12:00:00 AM

Nothing odd happens when it gets converted to date:

select convert(date, dateadd(day, -((datepart(cdw, '2017-09-12') + 2) % 7), '2017-09-12'))

9/8/2017


Using a variable

I've found that assigning the calculated date to a variable first does allow the query to work. However, variables aren't an option in my case (and really, this just raises more questions for me - why does it work when the value is stored in a variable, but not when place directly in the query):

Select via @val

declare @val date
select @val = convert(date, dateadd(day, -((datepart(cdw, '2017-09-12') + 2) % 7), '2017-09-12'))
select * from #tmp1 where test_date = @val

9/8/2017

Value of @val:

select @val

9/8/2017

  • if you were to replace the hardcoded 2017-09-08 with 9/8/2017, 8/9/2017, 2017/9/8 and 2017/8/9 ... do any of these return the record? [wondering if IQ may need to be told what a 'valid' date format looks like]; alternatively, what shows up in the table if your insert uses one of these other date formats? – markp Oct 13 '17 at 15:51
  • @markp Hardcoding the variations with 9/8 work, but the 8/9 variations do not. Similarly, using an explicit "September 8, 2017" as both the value when inserting the row and for the "this date" value in the where clause for the calculation, has the same results as in the question. I don't think that the problem is a date format problem. On a side note, I just tried assigning the "Friday" date to a variable, and confirmed that works. I'll update the question to add that in. – RToyo Oct 13 '17 at 16:12
  • @markp Sorry, to clarify, using "9/8" uses the same dates that I was expecting, whereas 8/9 gets interpreted as August 9th. Nothing changes with the query results when I use 9/8. – RToyo Oct 13 '17 at 16:18
  • So, it sounds like using a variable may be the solution to your problem? If it is, then please go ahead and take that out of the question, and add it in as an answer. If it isn't, please clarify what the remaining question is. Thanks! – RDFozz Oct 13 '17 at 19:49
  • 1
    I'd update the question to make it clear that this is purely additional information: that using a variable isn't a valid workaround in your case, and that you'd still like to know why IQ and ASE are behaving differently. Others may read the last part of your post and assume that it is a valid answer. – RDFozz Oct 13 '17 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.