Dates in sql

How to add/subtract from a datestamp field

[johnyfav]

On my site I log every user with cookies and an entry in to our sql database with a seesion id. If that user then buys something I change their status to 1 from 0. I hpoe this making sense.

I want to see how many users brought something in the lasy x days for reporting.

I have the datstamp field in the table already in the format (ddmmyyyy) and I can't for the life of me work out how I would subtract from today!

Can anyone help?

Thanks in advance!

[topr8]

CURRENT_TIMESTAMP - 3 = 3 days ago

pseudo code:
select * from table where timefield greater than [CURRENT_TIMESTAMP - x]

[lovethecoast]

If you're referring to SQL Server,

SELECT * FROM table WHERE DATEDIFF(d, dateColumn, GETDATE()) <= 1

(shows all of yesterday and today) or

SELECT * FROM table WHERE DATEDIFF(hh, dateColumn, GETDATE()) <= 24

(shows last 24 hours only)

S

[johnyfav]

They are in fact both the answer I was hoping for.

However it would seem there are 2 columns in the table; a datestamp and a timestamp. Looking atthe properties of the fields it says they are int.

As such the above sql one isn't working, i haven't tried the asp version as yet!

Any ideas out to get it work in sql first?

Thanks once again for your help!

[topr8]

i think you can convert values to integers in sqlServer something like

CONVERT(INT,value_to_convert)

[jimilives]

My 2 cents is to set your date via asp, then interject it into the sql

<% xdate = date - 3 %>

Select * where DATECOLUMN > ' & xdate & '

Hope that makes sense, just waking up so code isnt exact ;)

[carguy84]

I'd respectfully disagree with jimi, you want the least amount of processing going on in the ASP, and the most amount of processing going on in T-SQL. But whichever way you choose to do it, keep it consistent throughout your site.

The date and time are in two seperate columns, and they are type INT? Can you post some examples?

Chip-