I have trouble to get query which returns last Friday in Sybase IQ. The following query works on MSSQL but not in sybase IQ. How do I modify it so it works on IQ? Get last Friday (If GETDATE() is Friday, then stay as it is SELECT CASE WHEN DATEPART(dw,GETDATE()) >= 5 THEN (SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)+4) ELSE (SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)3) END asked 04 Mar '14, 14:01 sqlgeek 
IMO using zero to represent 19000101 is nonsensical (as described by the second answer to this StackOverflow question), and it isn't documented in the SQL Server 2012 docs for DATEDIFF but it DOES WORK according to the first answer to the aformentioned StackOverflow question... and it DOES work according to actual testing: 1> SELECT 2> CASE WHEN DATEPART(dw,GETDATE()) >= 5 THEN 3> (SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)+4) 4> ELSE 5> (SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)3) 6> END 7> go  20140228 00:00:00.000 (1 row affected) 1> Whether that's the right answer, I dunno, too lazy to check :) Anyway, as you have discovered, it mos' def does NOT work in SQL Anywhere: SELECT CASE WHEN DATEPART(dw,GETDATE()) >= 5 THEN (SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)+4) ELSE (SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)3) END Could not execute statement. Cannot convert 0 to a date Replacing all the "0"s with "19000101" in both the DATEADD and DATEDIFF calls for SQL Anywhere 16... SELECT CASE WHEN DATEPART(dw,GETDATE()) >= 5 THEN (SELECT DATEADD(wk, DATEDIFF(wk,'19000101',GETDATE()), '19000101')+4) ELSE (SELECT DATEADD(wk, DATEDIFF(wk,'19000101',GETDATE()), '19000101')3) END AS calculated_date; calculated_date '20140228 00:00:00.000' Bingo! At least it's the same as the SQL Server answer... I still have no idea how the code works, so let's test it... BEGIN DECLARE @getdate DATE; DECLARE @calcdate DATE; SET @getdate = '20140301'; WHILE @getdate <= '20140331' LOOP SELECT CASE WHEN DATEPART(dw,@getdate) >= 5 THEN (SELECT DATEADD(wk, DATEDIFF(wk,'19000101',@getdate), '19000101')+4) ELSE (SELECT DATEADD(wk, DATEDIFF(wk,'19000101',@getdate), '19000101')3) END AS calculated_date INTO @calcdate; MESSAGE STRING ( @getdate, ' is a ', DAYNAME ( @getdate ), ', and ', @calcdate, ' is a ', DAYNAME ( @calcdate ) ) TO CLIENT; SET @getdate = @getdate + 1; END LOOP; END; 20140301 is a Saturday, and 20140228 is a Friday 20140302 is a Sunday, and 20140228 is a Friday 20140303 is a Monday, and 20140228 is a Friday 20140304 is a Tuesday, and 20140228 is a Friday 20140305 is a Wednesday, and 20140228 is a Friday 20140306 is a Thursday, and 20140307 is a Friday 20140307 is a Friday, and 20140307 is a Friday 20140308 is a Saturday, and 20140307 is a Friday 20140309 is a Sunday, and 20140307 is a Friday 20140310 is a Monday, and 20140307 is a Friday 20140311 is a Tuesday, and 20140307 is a Friday 20140312 is a Wednesday, and 20140307 is a Friday 20140313 is a Thursday, and 20140314 is a Friday 20140314 is a Friday, and 20140314 is a Friday 20140315 is a Saturday, and 20140314 is a Friday 20140316 is a Sunday, and 20140314 is a Friday 20140317 is a Monday, and 20140314 is a Friday 20140318 is a Tuesday, and 20140314 is a Friday 20140319 is a Wednesday, and 20140314 is a Friday 20140320 is a Thursday, and 20140321 is a Friday 20140321 is a Friday, and 20140321 is a Friday 20140322 is a Saturday, and 20140321 is a Friday 20140323 is a Sunday, and 20140321 is a Friday 20140324 is a Monday, and 20140321 is a Friday 20140325 is a Tuesday, and 20140321 is a Friday 20140326 is a Wednesday, and 20140321 is a Friday 20140327 is a Thursday, and 20140328 is a Friday 20140328 is a Friday, and 20140328 is a Friday 20140329 is a Saturday, and 20140328 is a Friday 20140330 is a Sunday, and 20140328 is a Friday 20140331 is a Monday, and 20140328 is a Friday 1 row(s) affected Execution time: 0.022 seconds Close, but no cigar. The requirements say "Get last Friday (If GETDATE() is Friday, then stay as it is" so the Thursday answers all look wrong. SQL Anywhere doesn't have a SET DATEFIRST option, and maybe that's been changed in your SQL Server database to make Monday the first day of the week instead of Sunday, which is what SQL Anywhere uses: Weekday DW 17 (Sunday = 1, ..., Saturday = 7) Anyway, changing the 5 to a 6 seems to make it work in SQL Anywhere, and I will bet Surströmming it will work in IQ: BEGIN DECLARE @getdate DATE; DECLARE @calcdate DATE; SET @getdate = '20140301'; WHILE @getdate <= '20140331' LOOP SELECT CASE WHEN DATEPART(dw,@getdate) >= 6 THEN (SELECT DATEADD(wk, DATEDIFF(wk,'19000101',@getdate), '19000101')+4) ELSE (SELECT DATEADD(wk, DATEDIFF(wk,'19000101',@getdate), '19000101')3) END AS calculated_date INTO @calcdate; MESSAGE STRING ( @getdate, ' is a ', DAYNAME ( @getdate ), ', and ', @calcdate, ' is a ', DAYNAME ( @calcdate ) ) TO CLIENT; SET @getdate = @getdate + 1; END LOOP; END; 20140301 is a Saturday, and 20140228 is a Friday 20140302 is a Sunday, and 20140228 is a Friday 20140303 is a Monday, and 20140228 is a Friday 20140304 is a Tuesday, and 20140228 is a Friday 20140305 is a Wednesday, and 20140228 is a Friday 20140306 is a Thursday, and 20140228 is a Friday 20140307 is a Friday, and 20140307 is a Friday 20140308 is a Saturday, and 20140307 is a Friday 20140309 is a Sunday, and 20140307 is a Friday 20140310 is a Monday, and 20140307 is a Friday 20140311 is a Tuesday, and 20140307 is a Friday 20140312 is a Wednesday, and 20140307 is a Friday 20140313 is a Thursday, and 20140307 is a Friday 20140314 is a Friday, and 20140314 is a Friday 20140315 is a Saturday, and 20140314 is a Friday 20140316 is a Sunday, and 20140314 is a Friday 20140317 is a Monday, and 20140314 is a Friday 20140318 is a Tuesday, and 20140314 is a Friday 20140319 is a Wednesday, and 20140314 is a Friday 20140320 is a Thursday, and 20140314 is a Friday 20140321 is a Friday, and 20140321 is a Friday 20140322 is a Saturday, and 20140321 is a Friday 20140323 is a Sunday, and 20140321 is a Friday 20140324 is a Monday, and 20140321 is a Friday 20140325 is a Tuesday, and 20140321 is a Friday 20140326 is a Wednesday, and 20140321 is a Friday 20140327 is a Thursday, and 20140321 is a Friday 20140328 is a Friday, and 20140328 is a Friday 20140329 is a Saturday, and 20140328 is a Friday 20140330 is a Sunday, and 20140328 is a Friday 20140331 is a Monday, and 20140328 is a Friday 1 row(s) affected Execution time: 0.019 seconds No, wait! SQL Anywhere DOES have a funky "SET DATEFIRST" statement which seems to work like a connectionlevel SET TEMPORARY OPTION statement. Anywhere, leaving the "5" alone in your code, and adding a SET DATEFIRST 1 statement (making the week start on a Monday) also makes the test work: BEGIN DECLARE @getdate DATE; DECLARE @calcdate DATE; SET DATEFIRST 1; SET @getdate = '20140301'; WHILE @getdate <= '20140331' LOOP SELECT CASE WHEN DATEPART(dw,@getdate) >= 5 THEN (SELECT DATEADD(wk, DATEDIFF(wk,'19000101',@getdate), '19000101')+4) ELSE (SELECT DATEADD(wk, DATEDIFF(wk,'19000101',@getdate), '19000101')3) END AS calculated_date INTO @calcdate; MESSAGE STRING ( @getdate, ' is a ', DAYNAME ( @getdate ), ', and ', @calcdate, ' is a ', DAYNAME ( @calcdate ) ) TO CLIENT; SET @getdate = @getdate + 1; END LOOP; END; 20140301 is a Saturday, and 20140228 is a Friday 20140302 is a Sunday, and 20140307 is a Friday 20140303 is a Monday, and 20140228 is a Friday 20140304 is a Tuesday, and 20140228 is a Friday 20140305 is a Wednesday, and 20140228 is a Friday 20140306 is a Thursday, and 20140228 is a Friday 20140307 is a Friday, and 20140307 is a Friday 20140308 is a Saturday, and 20140307 is a Friday 20140309 is a Sunday, and 20140314 is a Friday 20140310 is a Monday, and 20140307 is a Friday 20140311 is a Tuesday, and 20140307 is a Friday 20140312 is a Wednesday, and 20140307 is a Friday 20140313 is a Thursday, and 20140307 is a Friday 20140314 is a Friday, and 20140314 is a Friday 20140315 is a Saturday, and 20140314 is a Friday 20140316 is a Sunday, and 20140321 is a Friday 20140317 is a Monday, and 20140314 is a Friday 20140318 is a Tuesday, and 20140314 is a Friday 20140319 is a Wednesday, and 20140314 is a Friday 20140320 is a Thursday, and 20140314 is a Friday 20140321 is a Friday, and 20140321 is a Friday 20140322 is a Saturday, and 20140321 is a Friday 20140323 is a Sunday, and 20140328 is a Friday 20140324 is a Monday, and 20140321 is a Friday 20140325 is a Tuesday, and 20140321 is a Friday 20140326 is a Wednesday, and 20140321 is a Friday 20140327 is a Thursday, and 20140321 is a Friday 20140328 is a Friday, and 20140328 is a Friday 20140329 is a Saturday, and 20140328 is a Friday 20140330 is a Sunday, and 20140404 is a Friday 20140331 is a Monday, and 20140328 is a Friday 1 row(s) affected Execution time: 0.019 seconds I will leave it up to other folks to propose a lessinscrutable solution ( a scrutable solution? :) answered 04 Mar '14, 16:12 Breck Carter Thanks Breck! I did not know 0 can translate to 19000101 in sybase. This answer really improves my understanding.
(05 Mar '14, 09:21)
sqlgeek
Replies hidden
To clarify: It is SQL Server, not SQL Anywhere, that treats an integer N as being 19000101 + N days (hence 0 is treated as 19000101). That's why the zeroes had to be changed to '19000101' in SQL Anywhere.
(05 Mar '14, 11:20)
Breck Carter

Put a challenge before a mathematician and voila: select dateadd(day,(MOD(datepart(weekday, getdate()),7)+1), getdate()) answered 05 Mar '14, 16:20 JBSchueler FWIW: For a solution independent of the "first_day_of_week" database option, I suggest to use the simpler DOW() function:
However, the solution above seems to misbehave on Fridays  it returns the previous Friday, whereas the question says
So I guess a ("scrutable"?) solution would be: select dateadd(day, MOD(DOW(getdate()) + 1, 7), getdate()) The expression "DOW(getdate()) + 1" returns 2 for Sunday, 3 for Monday, ...6 for Thursday, 7 for Friday and 8 for Saturday. The remainder MOD 7 is then 0 for Friday and 1 for Saturday, so exactly the number of days to subtract from today.
(06 Mar '14, 06:00)
Volker Barth
Re: If GETDATE() is Friday, then stay as it is Dang, didn't read the specs closely enough. Your revision does the trick.
(06 Mar '14, 09:33)
JBSchueler

Found the solution by myself. it was a little tricky. 19000105 is the first Friday. Select DateAdd(dd, ((DateDiff(dd, '19000105', GETDATE()) ) / 7)*7,'19000105') answered 04 Mar '14, 16:02 sqlgeek 1
Yeah, that works in SQL Anywhere too, no SET DATEFIRST 1 required (but I still haven't the foggiest how it works :)... BEGIN DECLARE @getdate DATE; DECLARE @calcdate DATE; SET @getdate = '20140301'; WHILE @getdate <= '20140331' LOOP Select DateAdd(dd, ((DateDiff(dd, '19000105', @getdate) ) / 7)*7,'19000105') AS calculated_date INTO @calcdate; MESSAGE STRING ( @getdate, ' is a ', DAYNAME ( @getdate ), ', and ', @calcdate, ' is a ', DAYNAME ( @calcdate ) ) TO CLIENT; SET @getdate = @getdate + 1; END LOOP; END; 20140301 is a Saturday, and 20140228 is a Friday 20140302 is a Sunday, and 20140228 is a Friday 20140303 is a Monday, and 20140228 is a Friday 20140304 is a Tuesday, and 20140228 is a Friday 20140305 is a Wednesday, and 20140228 is a Friday 20140306 is a Thursday, and 20140228 is a Friday 20140307 is a Friday, and 20140307 is a Friday 20140308 is a Saturday, and 20140307 is a Friday 20140309 is a Sunday, and 20140307 is a Friday 20140310 is a Monday, and 20140307 is a Friday 20140311 is a Tuesday, and 20140307 is a Friday 20140312 is a Wednesday, and 20140307 is a Friday 20140313 is a Thursday, and 20140307 is a Friday 20140314 is a Friday, and 20140314 is a Friday 20140315 is a Saturday, and 20140314 is a Friday 20140316 is a Sunday, and 20140314 is a Friday 20140317 is a Monday, and 20140314 is a Friday 20140318 is a Tuesday, and 20140314 is a Friday 20140319 is a Wednesday, and 20140314 is a Friday 20140320 is a Thursday, and 20140314 is a Friday 20140321 is a Friday, and 20140321 is a Friday 20140322 is a Saturday, and 20140321 is a Friday 20140323 is a Sunday, and 20140321 is a Friday 20140324 is a Monday, and 20140321 is a Friday 20140325 is a Tuesday, and 20140321 is a Friday 20140326 is a Wednesday, and 20140321 is a Friday 20140327 is a Thursday, and 20140321 is a Friday 20140328 is a Friday, and 20140328 is a Friday 20140329 is a Saturday, and 20140328 is a Friday 20140330 is a Sunday, and 20140328 is a Friday 20140331 is a Monday, and 20140328 is a Friday 1 row(s) affected Execution time: 0.01 seconds
(04 Mar '14, 16:20)
Breck Carter

This is a SQL Anywhere forum, but if the solution isn't the same in both IQ and SQL Anywhere, I'll eat Surströmming! :)