SQL Query Date time Suggestion

Question

I have a SQL which gets data until yesterday by using where clause as " Where tpd."RELATIVE_GLOBAL_DAY" = -1 " The db is defined as such that when I want to get todays data, I will have use 0 and likewise for yesterdays data, I can use -1 and for day before -2.

My query works fine except on monday. As per the query it gives me data of Sunday which I don't want. Instead I need data from last monday to friday all together but when its Tuesday I only need data for previous day which in this case should be Monday.

How do I put this condition in the where clause.

Thanks

tpd."RELATIVE_GLOBAL_DAY" = -1

Note that [sql] is general / standard SQL but answers to this question are likely database-specific; also [teradata-sql-assistant] is a client. Please tag the specific database being used such as [sql-server], [oracle], etc. for better answers. — Fred
– Fred, Commented Feb 5, 2024 at 23:49

Jānis Lazovskis · Accepted Answer · 2024-02-05 11:18:59Z

1

As long as you have a column which has the current weekday or the current date, you can use OR to take care of these two situations. For example:

WHERE (
    (day = 1 AND tpd."RELATIVE_GLOBAL_DAY" IN (-3,-4,-5,-6,-7)) OR
    (day > 1 AND tpd."RELATIVE_GLOBAL_DAY" = -1)
)

Here I am assuming you have a column day which indicates the current day of the week. If you do not, but you have a column date which indicates the current date, replace day above with DATEPART('weekday',date).

answered Feb 5, 2024 at 11:18

Jānis Lazovskis

234 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fred Over a year ago

For SQL Server, DATEPART values depend on locale DATEFIRST settings. For Teradata, better to use td_day_of_week or DayOfWeek which always return 1 for Sunday, 2 for Monday, etc.

Virendra Over a year ago

Thanks Janis, This is really helpful. One last question: How do i extract day info from current_date. Eg. Today is 2/6/2024 and I want to have TUE as the output Thanks

Jānis Lazovskis Over a year ago

@Virendra if you want the full name of the day (like Tuesday), you can use DATENAME(weekday,current_date), and if you want just the first three letters capitalized, you can use UPPER(SUBSTRING(DATENAME(wd,current_date),1,3)). The functions may be different for your specific database, but the function names are probably something similar to this (for example, in Redshift it would be TO_CHAR(current_date, 'Day') instead of the DATENAME function).

Collectives™ on Stack Overflow

SQL Query Date time Suggestion

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related