January 2, 2008 at 3:26 am
this works fine..
SELECT *, DateRecorded AS Expr1
FROM tbl_ISALOG_SingleUser
WHERE (DateRecorded < GETDATE())
but this doesnt
SELECT *, DateRecorded AS Expr1
FROM tbl_ISALOG_SingleUser
WHERE (DateRecorded < GETDATE()-3)
why won't getdate()-3 work..
January 2, 2008 at 4:09 am
hb21l6 (1/2/2008)
this works fine..SELECT *, DateRecorded AS Expr1
FROM tbl_ISALOG_SingleUser
WHERE (DateRecorded < GETDATE())
but this doesnt
SELECT *, DateRecorded AS Expr1
FROM tbl_ISALOG_SingleUser
WHERE (DateRecorded < GETDATE()-3)
why won't getdate()-3 work..
What do you mean by "why won't getdate()-3 work.. "?
Did you get any error?
Failing to plan is Planning to fail
January 2, 2008 at 8:16 am
well, it doesnt give an error as such, it just stick a few spaces in the SQL.
e.g. = "getdate()-3" changes to "Getdate() - 3"
this is the query...
SELECT *, DateRec AS Expr1
FROM tbl_ISALOG_SingleUser
WHERE (CONVERT(nvarchar, DateRec, 103) < CONVERT(nvarchar, GETDATE() - 3, 103))
it doesnt return any rows.. even thou there are rows there!!
I'm sure i've done this before on SQL and its worked fine
>:O( grr.
January 2, 2008 at 8:31 am
hb21l6 (1/2/2008)
well, it doesnt give an error as such, it just stick a few spaces in the SQL.e.g. = "getdate()-3" changes to "Getdate() - 3"
this is the query...
SELECT *, DateRec AS Expr1
FROM tbl_ISALOG_SingleUser
WHERE (CONVERT(nvarchar, DateRec, 103) < CONVERT(nvarchar, GETDATE() - 3, 103))
it doesnt return any rows.. even thou there are rows there!!
I'm sure i've done this before on SQL and its worked fine
>:O( grr.
The query in this one is very much different from the one in your first question. Here you are converting to nvarchar and then comparing the nvarchar to another nvarchar lexicographically! Depending on your language setting, the order of the day, month and year values will not be in year,month, day order, so you may end up comparing "01/01/2008" with "12/29/2007" and the first value will be lower, even though the corresponding date is later. Why are you converting to nvarchar?
Regards,
Andras
January 2, 2008 at 8:56 am
thanks for the reply Andras.
I need to delete data that is 3 days old from the database. I'm just using a select to show the data to start with before changing it to a delete.
the data field is datetime. 01/02/2007 13:23:18.
i was just trying to delete everything for a specific date that is 3 days old.
I assumed that I could use a convert and nvarchar to make it a date of 01/02/2007.. stripping off the time basically. other wise, i'll have to do a range from midnight to midnight the following day..
unless you can think of a better way?
many thanks
Dave
January 2, 2008 at 9:43 am
hb21l6 (1/2/2008)
thanks for the reply Andras.I need to delete data that is 3 days old from the database. I'm just using a select to show the data to start with before changing it to a delete.
the data field is datetime. 01/02/2007 13:23:18.
i was just trying to delete everything for a specific date that is 3 days old.
I assumed that I could use a convert and nvarchar to make it a date of 01/02/2007.. stripping off the time basically. other wise, i'll have to do a range from midnight to midnight the following day..
unless you can think of a better way?
many thanks
Dave
Hi Dave,
you could use the DATEDIFF(dd, DateRecorded, getdate()) function.
Regards,
Andras
January 2, 2008 at 10:04 am
This should do what you want.
SELECT
*,
DateRecorded AS Expr1
FROM
tbl_ISALOG_SingleUser
WHERE
DateRecorded < dateadd(day,datediff(day,0,getdate())-3,0)
January 4, 2008 at 1:31 am
Many thanks for all your help guys, I've managed to get it working.
Very much appreciated!!
Cheers
Dave
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply