on 2014 Mar 04 2:01 PM
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
Request clarification before answering.
Put a challenge before a mathematician and voila:
select dateadd(day,-(MOD(datepart(weekday, getdate()),7)+1), getdate())
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
FWIW: For a solution independent of the "first_day_of_week" database option, I suggest to use the simpler DOW() function:
DOW function [Date and time]
Returns a number from 1 to 7 representing the day of the week of a date, where Sunday=1, Monday=2, and so on.
However, the solution above seems to misbehave on Fridays - it returns the previous Friday, whereas the question says
Get last Friday (If GETDATE() is Friday, then stay as it is
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.
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? 🙂
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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')
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
50 | |
9 | |
8 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.