Answers to: SYBASE IQ - Get last Friday queryhttps://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query<p>I have trouble to get query which returns last Friday in Sybase IQ.</p>
<p>The following query works on MSSQL but not in sybase IQ. How do I modify it so it works on IQ?</p>
<p>--Get last Friday (If GETDATE() is Friday, then stay as it is</p>
<div class="codehilite"><pre> <span class="n">SELECT</span>
<span class="n">CASE</span> <span class="n">WHEN</span> <span class="n">DATEPART</span><span class="p">(</span><span class="n">dw</span><span class="p">,</span><span class="n">GETDATE</span><span class="p">())</span> <span class="o">>=</span> <span class="mi">5</span> <span class="n">THEN</span>
<span class="p">(</span><span class="n">SELECT</span> <span class="n">DATEADD</span><span class="p">(</span><span class="n">wk</span><span class="p">,</span> <span class="n">DATEDIFF</span><span class="p">(</span><span class="n">wk</span><span class="p">,</span><span class="mi">0</span><span class="p">,</span><span class="n">GETDATE</span><span class="p">()),</span> <span class="mi">0</span><span class="p">)</span><span class="o">+</span><span class="mi">4</span><span class="p">)</span>
<span class="n">ELSE</span>
<span class="p">(</span><span class="n">SELECT</span> <span class="n">DATEADD</span><span class="p">(</span><span class="n">wk</span><span class="p">,</span> <span class="n">DATEDIFF</span><span class="p">(</span><span class="n">wk</span><span class="p">,</span><span class="mi">0</span><span class="p">,</span><span class="n">GETDATE</span><span class="p">()),</span> <span class="mi">0</span><span class="p">)</span><span class="o">-</span><span class="mi">3</span><span class="p">)</span>
<span class="k">END</span>
</pre></div>enThu, 06 Mar 2014 09:33:36 -0500Comment by JBSchueler on JBSchueler's answerhttps://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query#20601<p>Re: If GETDATE() is Friday, then stay as it is</p>
<p>Dang, didn't read the specs closely enough. Your revision does the trick.</p>JBSchuelerThu, 06 Mar 2014 09:33:36 -0500https://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query#20601Comment by Volker Barth on JBSchueler's answerhttps://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query#20593<p>FWIW: For a solution independent of the "first_day_of_week" database option, I suggest to use the simpler DOW() function:</p>
<blockquote>
<p><strong>DOW function [Date and time]</strong><br>
</p>
<p>Returns a number from 1 to 7 representing the day of the week of a date, where Sunday=1, Monday=2, and so on.</p>
</blockquote>
<p>However, the solution above seems to misbehave on Fridays - it returns the previous Friday, whereas the question says</p>
<blockquote>
<p>Get last Friday (If GETDATE() is Friday, then stay as it is</p>
</blockquote>
<p>So I guess a ("scrutable"?) solution would be:</p>
<div class="codehilite"><pre><span class="k">select</span> <span class="n">dateadd</span><span class="p">(</span><span class="k">day</span><span class="p">,</span> <span class="o">-</span><span class="k">MOD</span><span class="p">(</span><span class="n">DOW</span><span class="p">(</span><span class="n">getdate</span><span class="p">())</span> <span class="o">+</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">7</span><span class="p">),</span> <span class="n">getdate</span><span class="p">())</span>
</pre></div>
<p>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.</p>Volker BarthThu, 06 Mar 2014 06:00:15 -0500https://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query#20593Answer by JBSchuelerhttps://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query/20590<p>Put a challenge before a mathematician and voila:</p>
<div class="codehilite"><pre><span class="nb">select</span> <span class="n">dateadd</span><span class="p">(</span><span class="n">day</span><span class="p">,</span><span class="o">-</span><span class="p">(</span><span class="n">MOD</span><span class="p">(</span><span class="n">datepart</span><span class="p">(</span><span class="n">weekday</span><span class="p">,</span> <span class="n">getdate</span><span class="p">()),</span><span class="mi">7</span><span class="p">)</span><span class="o">+</span><span class="mi">1</span><span class="p">),</span> <span class="n">getdate</span><span class="p">())</span>
</pre></div>JBSchuelerWed, 05 Mar 2014 16:20:46 -0500https://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query/20590Comment by Breck Carter on sqlgeek's questionhttps://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query#20576<p>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.</p>Breck CarterWed, 05 Mar 2014 11:20:40 -0500https://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query#20576Comment by sqlgeek on Breck Carter's answerhttps://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query#20573<p>Thanks Breck! I did not know 0 can translate to 1900-01-01 in sybase. This answer really improves my understanding.<br>
</p>sqlgeekWed, 05 Mar 2014 09:21:13 -0500https://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query#20573Comment by Breck Carter on sqlgeek's answerhttps://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query#20554<p>Yeah, that works in SQL Anywhere too, no SET DATEFIRST 1 required (but I still haven't the foggiest how it works :)...</p>
<pre>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
</pre>Breck CarterTue, 04 Mar 2014 16:20:56 -0500https://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query#20554Answer by Breck Carterhttps://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query/20552<p>IMO using zero to represent 1900-01-01 is nonsensical (as described by the second answer to <a href="http://stackoverflow.com/questions/11955481/confusion-over-the-second-argument-of-the-datediff-function">this StackOverflow question</a>), and it isn't documented in <a href="http://technet.microsoft.com/en-us/library/ms189794.aspx">the SQL Server 2012 docs for DATEDIFF</a> but it DOES WORK according to the first answer to the aformentioned StackOverflow question... and it DOES work according to actual testing:</p>
<pre>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>
</pre>
<p>Whether that's the right answer, I dunno, too lazy to check :)</p>
<p>Anyway, as you have discovered, it mos' def does NOT work in SQL Anywhere:</p>
<pre>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
</pre>
<p>Replacing all the "0"s with "1900-01-01" in both the DATEADD and DATEDIFF calls for SQL Anywhere 16...</p>
<pre>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'
</pre>
<p>Bingo!</p>
<p>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...</p>
<pre>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
</pre>
<p>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.</p>
<p>SQL Anywhere doesn't have a <a href="http://msdn.microsoft.com/en-us/library/ms181598.aspx">SET DATEFIRST option</a>, 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: <a href="http://msdn.microsoft.com/en-us/library/ms181598.aspx">Weekday DW 1-7 (Sunday = 1, ..., Saturday = 7)</a> </p>
<p>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:</p>
<pre>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
</pre>
<p>No, wait! SQL Anywhere DOES have <a href="http://dcx.sybase.com/index.html#sa160/en/dbreference/set-tsql-statement.html">a funky "SET DATEFIRST" statement</a> 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:</p>
<pre>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
</pre>
<p>I will leave it up to other folks to propose a less-inscrutable solution ( a scrutable solution? :)</p>Breck CarterTue, 04 Mar 2014 16:12:38 -0500https://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query/20552Answer by sqlgeekhttps://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query/20551<p>Found the solution by myself. it was a little tricky. 19000105 is the first Friday.</p>
<p>Select DateAdd(dd, ((DateDiff(dd, '19000105', GETDATE()) ) / 7)*7,'19000105')</p>sqlgeekTue, 04 Mar 2014 16:02:28 -0500https://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query/20551Comment by Breck Carter on sqlgeek's questionhttps://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query#20550<p>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! :)</p>Breck CarterTue, 04 Mar 2014 14:54:12 -0500https://sqlanywhere-forum.sap.com/questions/20548/sybase-iq-get-last-friday-query#20550