SYBASE IQ - Get last Friday query

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

sybase-iq

asked 04 Mar '14, 14:01

sqlgeek
147●7●7●13
accept rate: 0%

edited 04 Mar '14, 14:02

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! :)

(04 Mar '14, 14:54) Breck Carter

flat view

3 Answers:

active answers oldest answers newest answers popular answers

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? :)

permanent link

answered 04 Mar '14, 16:12

Breck Carter
32.5k●539●724●1050
accept rate: 20%

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

flat view

Put a challenge before a mathematician and voila:

select dateadd(day,-(MOD(datepart(weekday, getdate()),7)+1), getdate())

permanent link

answered 05 Mar '14, 16:20

JBSchueler
3.1k●2●12●58
accept rate: 19%

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.

(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

flat view

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')

permanent link

answered 04 Mar '14, 16:02

sqlgeek
147●7●7●13
accept rate: 0%

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

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sybase-iq ×40

question asked: 04 Mar '14, 14:01

question was seen: 11,764 times

last updated: 06 Mar '14, 09:33

SAP SQL Anywhere

SYBASE IQ - Get last Friday query

Follow this question

Related questions