CODEWITHSUNDEEP
sqlsql-server
vico
vico

Reputation: 18201

Get date of 3 days ago

I have SQL script that selects everything from current day.

SELECT  [ClientID] from [logs] where Date > CONVERT (date, SYSDATETIME())

Date is type of DateTime.

How to get everything within last 3 days? I suppose I need subtract 3 days from function SYSDATETIME() result, but how?

Upvotes: 21

Views: 97588

Answers (7)

amir avira
amir avira

Reputation: 59

In my case:

select * from Table where row > now() - INTERVAL 3 day;

So you can fetch all of 3 days ago!

Upvotes: 0

Tharif
Tharif

Reputation: 13971

Use GETDATE() : Yes, it gets date from system!

Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

Query:

SELECT  [ClientID] from [logs] where ( Date  > GETDATE() - 3)

More Reference:

GETDATE Detailed Documentation

Upvotes: 14

anirban karak
anirban karak

Reputation: 740

For mysql use this:

SELECT DATE_ADD(CURRENT_DATE, INTERVAL - 3 DAY);

Upvotes: 4

Madhivanan
Madhivanan

Reputation: 13700

SELECT  [ClientID] from [logs] where Date > DATEADD(day, -3, SYSDATETIME())

Upvotes: 0

Tom
Tom

Reputation: 747

Using BETWEEN is nice. I also prefer the DATEADD function. But be aware of the fact that the SYSDATETIME function (or I would us GETDATE()) also includes the time which would mean that events before the current time but within the three day period may not be included. You may have to convert both sides to a date instead of datetime.

Upvotes: 0

Matt
Matt

Reputation: 15061

Use BETWEEN

SELECT ClientID 
FROM logs
WHERE Date BETWEEN SYSDATETIME() AND SYSDATETIME() - 3

Upvotes: 1

Backs
Backs

Reputation: 24913

SELECT  [ClientID] from [logs] where Date > DATEADD(day, -3, CONVERT (date, SYSDATETIME()))

Upvotes: 26

Related Questions