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 |
IMO using zero to represent 1900-01-01 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 ----------------------- 2014-02-28 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 "1900-01-01" in both the DATEADD and DATEDIFF calls for SQL Anywhere 16... SELECT CASE WHEN DATEPART(dw,GETDATE()) >= 5 THEN (SELECT DATEADD(wk, DATEDIFF(wk,'1900-01-01',GETDATE()), '1900-01-01')+4) ELSE (SELECT DATEADD(wk, DATEDIFF(wk,'1900-01-01',GETDATE()), '1900-01-01')-3) END AS calculated_date; calculated_date '2014-02-28 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 = '2014-03-01'; WHILE @getdate <= '2014-03-31' LOOP SELECT CASE WHEN DATEPART(dw,@getdate) >= 5 THEN (SELECT DATEADD(wk, DATEDIFF(wk,'1900-01-01',@getdate), '1900-01-01')+4) ELSE (SELECT DATEADD(wk, DATEDIFF(wk,'1900-01-01',@getdate), '1900-01-01')-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; 2014-03-01 is a Saturday, and 2014-02-28 is a Friday 2014-03-02 is a Sunday, and 2014-02-28 is a Friday 2014-03-03 is a Monday, and 2014-02-28 is a Friday 2014-03-04 is a Tuesday, and 2014-02-28 is a Friday 2014-03-05 is a Wednesday, and 2014-02-28 is a Friday 2014-03-06 is a Thursday, and 2014-03-07 is a Friday 2014-03-07 is a Friday, and 2014-03-07 is a Friday 2014-03-08 is a Saturday, and 2014-03-07 is a Friday 2014-03-09 is a Sunday, and 2014-03-07 is a Friday 2014-03-10 is a Monday, and 2014-03-07 is a Friday 2014-03-11 is a Tuesday, and 2014-03-07 is a Friday 2014-03-12 is a Wednesday, and 2014-03-07 is a Friday 2014-03-13 is a Thursday, and 2014-03-14 is a Friday 2014-03-14 is a Friday, and 2014-03-14 is a Friday 2014-03-15 is a Saturday, and 2014-03-14 is a Friday 2014-03-16 is a Sunday, and 2014-03-14 is a Friday 2014-03-17 is a Monday, and 2014-03-14 is a Friday 2014-03-18 is a Tuesday, and 2014-03-14 is a Friday 2014-03-19 is a Wednesday, and 2014-03-14 is a Friday 2014-03-20 is a Thursday, and 2014-03-21 is a Friday 2014-03-21 is a Friday, and 2014-03-21 is a Friday 2014-03-22 is a Saturday, and 2014-03-21 is a Friday 2014-03-23 is a Sunday, and 2014-03-21 is a Friday 2014-03-24 is a Monday, and 2014-03-21 is a Friday 2014-03-25 is a Tuesday, and 2014-03-21 is a Friday 2014-03-26 is a Wednesday, and 2014-03-21 is a Friday 2014-03-27 is a Thursday, and 2014-03-28 is a Friday 2014-03-28 is a Friday, and 2014-03-28 is a Friday 2014-03-29 is a Saturday, and 2014-03-28 is a Friday 2014-03-30 is a Sunday, and 2014-03-28 is a Friday 2014-03-31 is a Monday, and 2014-03-28 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 1-7 (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 = '2014-03-01'; WHILE @getdate <= '2014-03-31' LOOP SELECT CASE WHEN DATEPART(dw,@getdate) >= 6 THEN (SELECT DATEADD(wk, DATEDIFF(wk,'1900-01-01',@getdate), '1900-01-01')+4) ELSE (SELECT DATEADD(wk, DATEDIFF(wk,'1900-01-01',@getdate), '1900-01-01')-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; 2014-03-01 is a Saturday, and 2014-02-28 is a Friday 2014-03-02 is a Sunday, and 2014-02-28 is a Friday 2014-03-03 is a Monday, and 2014-02-28 is a Friday 2014-03-04 is a Tuesday, and 2014-02-28 is a Friday 2014-03-05 is a Wednesday, and 2014-02-28 is a Friday 2014-03-06 is a Thursday, and 2014-02-28 is a Friday 2014-03-07 is a Friday, and 2014-03-07 is a Friday 2014-03-08 is a Saturday, and 2014-03-07 is a Friday 2014-03-09 is a Sunday, and 2014-03-07 is a Friday 2014-03-10 is a Monday, and 2014-03-07 is a Friday 2014-03-11 is a Tuesday, and 2014-03-07 is a Friday 2014-03-12 is a Wednesday, and 2014-03-07 is a Friday 2014-03-13 is a Thursday, and 2014-03-07 is a Friday 2014-03-14 is a Friday, and 2014-03-14 is a Friday 2014-03-15 is a Saturday, and 2014-03-14 is a Friday 2014-03-16 is a Sunday, and 2014-03-14 is a Friday 2014-03-17 is a Monday, and 2014-03-14 is a Friday 2014-03-18 is a Tuesday, and 2014-03-14 is a Friday 2014-03-19 is a Wednesday, and 2014-03-14 is a Friday 2014-03-20 is a Thursday, and 2014-03-14 is a Friday 2014-03-21 is a Friday, and 2014-03-21 is a Friday 2014-03-22 is a Saturday, and 2014-03-21 is a Friday 2014-03-23 is a Sunday, and 2014-03-21 is a Friday 2014-03-24 is a Monday, and 2014-03-21 is a Friday 2014-03-25 is a Tuesday, and 2014-03-21 is a Friday 2014-03-26 is a Wednesday, and 2014-03-21 is a Friday 2014-03-27 is a Thursday, and 2014-03-21 is a Friday 2014-03-28 is a Friday, and 2014-03-28 is a Friday 2014-03-29 is a Saturday, and 2014-03-28 is a Friday 2014-03-30 is a Sunday, and 2014-03-28 is a Friday 2014-03-31 is a Monday, and 2014-03-28 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 connection-level 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 = '2014-03-01'; WHILE @getdate <= '2014-03-31' LOOP SELECT CASE WHEN DATEPART(dw,@getdate) >= 5 THEN (SELECT DATEADD(wk, DATEDIFF(wk,'1900-01-01',@getdate), '1900-01-01')+4) ELSE (SELECT DATEADD(wk, DATEDIFF(wk,'1900-01-01',@getdate), '1900-01-01')-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; 2014-03-01 is a Saturday, and 2014-02-28 is a Friday 2014-03-02 is a Sunday, and 2014-03-07 is a Friday 2014-03-03 is a Monday, and 2014-02-28 is a Friday 2014-03-04 is a Tuesday, and 2014-02-28 is a Friday 2014-03-05 is a Wednesday, and 2014-02-28 is a Friday 2014-03-06 is a Thursday, and 2014-02-28 is a Friday 2014-03-07 is a Friday, and 2014-03-07 is a Friday 2014-03-08 is a Saturday, and 2014-03-07 is a Friday 2014-03-09 is a Sunday, and 2014-03-14 is a Friday 2014-03-10 is a Monday, and 2014-03-07 is a Friday 2014-03-11 is a Tuesday, and 2014-03-07 is a Friday 2014-03-12 is a Wednesday, and 2014-03-07 is a Friday 2014-03-13 is a Thursday, and 2014-03-07 is a Friday 2014-03-14 is a Friday, and 2014-03-14 is a Friday 2014-03-15 is a Saturday, and 2014-03-14 is a Friday 2014-03-16 is a Sunday, and 2014-03-21 is a Friday 2014-03-17 is a Monday, and 2014-03-14 is a Friday 2014-03-18 is a Tuesday, and 2014-03-14 is a Friday 2014-03-19 is a Wednesday, and 2014-03-14 is a Friday 2014-03-20 is a Thursday, and 2014-03-14 is a Friday 2014-03-21 is a Friday, and 2014-03-21 is a Friday 2014-03-22 is a Saturday, and 2014-03-21 is a Friday 2014-03-23 is a Sunday, and 2014-03-28 is a Friday 2014-03-24 is a Monday, and 2014-03-21 is a Friday 2014-03-25 is a Tuesday, and 2014-03-21 is a Friday 2014-03-26 is a Wednesday, and 2014-03-21 is a Friday 2014-03-27 is a Thursday, and 2014-03-21 is a Friday 2014-03-28 is a Friday, and 2014-03-28 is a Friday 2014-03-29 is a Saturday, and 2014-03-28 is a Friday 2014-03-30 is a Sunday, and 2014-04-04 is a Friday 2014-03-31 is a Monday, and 2014-03-28 is a Friday 1 row(s) affected Execution time: 0.019 seconds I will leave it up to other folks to propose a less-inscrutable solution ( a scrutable solution? :) Thanks Breck! I did not know 0 can translate to 1900-01-01 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 1900-01-01 + N days (hence 0 is treated as 1900-01-01). That's why the zeroes had to be changed to '1900-01-01' 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()) 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') 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 = '2014-03-01'; WHILE @getdate <= '2014-03-31' 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; 2014-03-01 is a Saturday, and 2014-02-28 is a Friday 2014-03-02 is a Sunday, and 2014-02-28 is a Friday 2014-03-03 is a Monday, and 2014-02-28 is a Friday 2014-03-04 is a Tuesday, and 2014-02-28 is a Friday 2014-03-05 is a Wednesday, and 2014-02-28 is a Friday 2014-03-06 is a Thursday, and 2014-02-28 is a Friday 2014-03-07 is a Friday, and 2014-03-07 is a Friday 2014-03-08 is a Saturday, and 2014-03-07 is a Friday 2014-03-09 is a Sunday, and 2014-03-07 is a Friday 2014-03-10 is a Monday, and 2014-03-07 is a Friday 2014-03-11 is a Tuesday, and 2014-03-07 is a Friday 2014-03-12 is a Wednesday, and 2014-03-07 is a Friday 2014-03-13 is a Thursday, and 2014-03-07 is a Friday 2014-03-14 is a Friday, and 2014-03-14 is a Friday 2014-03-15 is a Saturday, and 2014-03-14 is a Friday 2014-03-16 is a Sunday, and 2014-03-14 is a Friday 2014-03-17 is a Monday, and 2014-03-14 is a Friday 2014-03-18 is a Tuesday, and 2014-03-14 is a Friday 2014-03-19 is a Wednesday, and 2014-03-14 is a Friday 2014-03-20 is a Thursday, and 2014-03-14 is a Friday 2014-03-21 is a Friday, and 2014-03-21 is a Friday 2014-03-22 is a Saturday, and 2014-03-21 is a Friday 2014-03-23 is a Sunday, and 2014-03-21 is a Friday 2014-03-24 is a Monday, and 2014-03-21 is a Friday 2014-03-25 is a Tuesday, and 2014-03-21 is a Friday 2014-03-26 is a Wednesday, and 2014-03-21 is a Friday 2014-03-27 is a Thursday, and 2014-03-21 is a Friday 2014-03-28 is a Friday, and 2014-03-28 is a Friday 2014-03-29 is a Saturday, and 2014-03-28 is a Friday 2014-03-30 is a Sunday, and 2014-03-28 is a Friday 2014-03-31 is a Monday, and 2014-03-28 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! :)