Monday, March 19, 2012

Newbie needs help with timestamp calculation

I'm a newbie, so please be gentle

I have a table that has a timestamp column that I can reference, e.g.,

SELECT *
FROM mytable
WHERE RECORDTIME < {TS '2006-05-01 00:00:00.000' }

This type of selection works.

What I want to do now is select the rows where the timestamp is less than 30 days prior to the current date instead of hardcoding a timestamp every time. I'm trying to select anything older than 30 days.

SELECT *
FROM myfile
WHERE RECORDTIME < [ current date's timestamp] - 30 days

I don't even know where to start so any help is greatly appreciated.

Thanks in advance,

Robert

I think I have it. This seems to work.

SELECT *
FROM myfile
WHERE RECORDTIME < ( cast( getdate() as datetime) - day(30) )

Please advise if you see anything that would be an issue.

Thanks!

|||

I don't think that it works exactly as the spec 'today less 30 days'.

Consider these two different results:

select getdate() - day(30), getdate()
select dateadd(day, -30, getdate()), getdate()

-- -
2006-04-30 15:44:45.890 2006-05-31 15:44:45.890

(1 row(s) affected)


-- --
2006-05-01 15:44:45.890 2006-05-31 15:44:45.890

(1 row(s) affected)

/Kenneth

No comments:

Post a Comment