Discussion Locked

SQL Datetime

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I would like to select data between a column "activated_datum" and one week before. The data is saved in datatime format (1999-05-12 10:23:05). Is there anyone who knows how to write this SQL question?

Hanna A

6 replies

by artful

You can use the AddDate() function -- look it up in BOL. Even simpler, though, is to use simple subtraction, as this example shows:

SELECT GetDate(), GetDate() - 7

Your requirement isn't quite clear to me (select data between a column "activated_datum" and one week before). That could mean several things.

It might look mean:

SELECT *
FROM someTables
WHERE activated_datum
BETWEEN GetDate() AND GetDate() - 7

or you might just begin with:

SELECT activated_datum, activated_datum - 7
FROM someTables

and then play around until you have what you need.

Arthur

by vikas.sharma

As its the date format is mentioned I guess "activated_datum" is not a date column.

select * from someTable
where to_date(activated_datum,'YYYY-MM-DD HH24:MI:SS') > sysdate - 7
/

by sqlhound

SELECT activated_datum
FROM TableName
where activated_datum between '1999-05-12' and ('1999-05-12' - 13)

If you're doing this on SQL Server, you might want to check Books On Line for usage of DatePart and GetDate functions.

by texasconnelly

If you are asking for a rows where the column "activated_datum" is in the last 7 days, try this:

select *
from TABLE_NAME
where activated_datum between dateadd(dd, -7, getDate()) and getDate()

This discussion is closed to new replies.