SQLTeam.com | Weblogs | Forums

Present Date in SQL

Other SQL Server Topics
#1

I'm a new SQL programmer, so sorry if i'm doing a basic question.
I've made a DB for a fictional store(for learning purposes), now i want to now how many products the store fixed on the last 7 days.
So i did a SELECT COUNT(products.Id_Fixes) FROM Products, Store WHERE products.id_products=store.id_products AND store.entry_date... /*and now i want SQL to grab present date and substract 7 to all entry_dates so i know how many products where fixed on the last 7 days.

Can someone help me with this please?

#2

I assume you have a MS SQL database so this is T-SQL:

You can use

SELECT GETDATE();

to get the date of the server.

You can use the function DATEADD to add a negative number of days.

SELECT DATEADD(day, -7, GETDATE());

Please note that you join the tables in the WHERE clause, that is not common anymore

SELECT
COUNT(products.Id_Fixes)
FROM Products
INNER JOIN Store ON products.id_products=store.id_products
WHERE store.entry_date >= DATEADD(day, -7, GETDATE());

Hopes this will get you to the result you want.

1 Like
#3

There are a few others you should be aware of:

Select local_datetime       = getdate()
     , current_datetime     = current_timestamp
     , local_datetime2      = sysdatetime()
     , local_datetimeoffset = sysdatetimeoffset()
     , utc_datetime         = getutcdate()
     , utc_datetime2        = sysutcdatetime()
     , utc_datetimeoffset   = sysdatetimeoffset() At Time Zone 'UTC';

Notice the AT TIME ZONE qualifier - which can be used to alter the value to a specified time zone.

#4

Thank you very much for the help, i was able to do it.
I'm not in MS SQL, i'm using MySQL so i was able to do it with:
...
AND store.entry_date >= DATE_ADD(CURDATE(), INTERVAL -7 DAY)
AND store.entry_date <= CURDATE();