CODEWITHSUNDEEP
sqlsql-serverdateselect
questionador
questionador

Reputation: 113

Select returns results from last day, on mondays doesn´t return friday

I have a select that returns a result from the previous day. everything is ok but, come to monday, it returns the result from sunday, and i need it to return the previous friday results instead.

i tried to go back 3 days when the datetime is monday, but it's not working.

this is where I started

SELECT no, nome, obrano, datafinal  
    FROM bo
    WHERE nmdos LIKE '%preço%'
    AND datafinal = DATEADD(day, -1, CONVERT(date, GETDATE()))

this is where i'm at

SELECT no, nome, obrano, datafinal  
    FROM bo
    WHERE nmdos LIKE '%preço%'
    AND datafinal = DATEADD(day, - case convert (datetime, 'Dy') when 'Mon' then 3 else 1 end, CONVERT(date, GETDATE()))

i just get conversion errors all over, don't get why

Upvotes: 2

Views: 104

Answers (2)

EzLo
EzLo

Reputation: 14199

Your problem is SELECT convert (datetime, 'Dy'). This already throws an error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Avoid doing date checks with names (like Monday, March, Jan, etc.), this can change depending on the configured language. The correct form you are searching (I think) is 

SELECT DATENAME(WEEKDAY, GETDATE()) -- Returns 'Tuesday'

A better solution is to set DATEFIRST to a known value (for the DATEPART WEEKDAY) and do the comparison with the day number. You can use a CASE to change the -1 to -3:

SET DATEFIRST 1 -- 1: Monday, 7: Sunday

SELECT 
    no, 
    nome, 
    obrano, 
    datafinal  
FROM 
    bo
WHERE 
    nmdos LIKE '%preço%' AND 
    datafinal = DATEADD(
        DAY,
        CASE WHEN DATEPART(WEEKDAY, GETDATE()) = 1 -- If today is monday
            THEN -3 ELSE -1 END
        CONVERT(DATE, GETDATE()))

Also it's recommended to write the full datepart keyword so it doesn't bring confusion (write WEEKDAY instead of DW, MONTH instead of M, etc.). You can't escape from masks such as yyyy-MM-dd though.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

This doesn't make sense:

case convert(datetime, 'Dy') when 'Mon'

You are converting the string 'Dy' to a date time and then comparing that to a string which is not a datetime.

That is not how convert works. I think you want:

case datename('Dy', getdate()) when 'Monday' . . .

Upvotes: 1

Related Questions