Previous day query

[Riaan]

Hi There,

I am a newbie on SQL qieries and I am trying to run a query on a date/time filed to get yesterdays data.

Here is my test query ran from SQL analyzer:

select * from mvs_store_all_d ata_time_change where mvs_creation_da te = {fn NOW() }

The error I am getting is:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

I assume it is to do with the format of this field I am trying to query and may need to be converted?

Any help will be much appreciated.

Cheers,

Riaan.

[Phil1234]

Riaan,
Try
select * from mvs_store_all_d ata_time_change
where mvs_creation_da te > (getdate()-1)

[lipsa]

try this one

select * from <table> where DATEPART(dd, <date_column>)= DATEPART(dd, getdate()-1)

<date_column>-where your date is saved
Explanation-let it be 03/08/2006 today,then DATEPART(dd, getdate()-1)
returns 02.and u can get the data of the previous day.
Regards,
Lipsa

[lipsa]

Try this one

select * from <table> where DATEPART(dd, <date_column>)= DATEPART(dd, getdate()-1)

<date_column>-your column name in database where u insert dates
Explanation-suppose its 03/08/2006 today.so ,DATEPART(dd, getdate()-1)will return 02.and u can do the comarison and get the data of previous day. :)
Regards,
Lipsa :)

[Riaan]

Hi There.

Ok, I have tried your suggestion, but get the following error:

"Syntax error converting datetime from character string."

Any further ideas :). Cheers, Riaan.

[Riaan]

Hi Phil. Ok I treid your suggestion, but get the following error "Syntax error converting datetime from character string.". Any more ideas :).

Cheers, Riaan.

[bhatnagarp]

Hi Phil. Ok I treid your suggestion, but get the following error "Syntax error converting datetime from character string.". Any more ideas :).

Cheers, Riaan.

Ho Riaan,

try this one...

select * from mvs_store_all_d ata_time_change
where convert(char,mv s_creation_date ,102) = convert(char,ge tdate()-1,102)

regards,
Pankaj Bhatnagar

[Riaan]

Ho Riaan,

try this one...

select * from mvs_store_all_d ata_time_change
where convert(char,mv s_creation_date ,102) = convert(char,ge tdate()-1,102)

regards,
Pankaj Bhatnagar

Hi Pankaj

Ok, I tried your suggestion and now I am not getting the error message, but no data is returned? Is this not a case of converting this field (which is a integer, to a string and then cast it as a datetime format?). If so, can you maybe help with the syntax of such a query? ALso can I do it from a view, where I am running this from, as I am also not sure where to enter it in the view?. Hope you can help.

Cheers,

Riaan.

[Riaan]

Hi Pankaj

Ok, I tried your suggestion and now I am not getting the error message, but no data is returned? Is this not a case of converting this field (which is a integer, to a string and then cast it as a datetime format?). If so, can you maybe help with the syntax of such a query? ALso can I do it from a view, where I am running this from, as I am also not sure where to enter it in the view?. Hope you can help.

Cheers,

Riaan.

[Lymedo]

select * from <table> where DATEPART(dd, <date_column>)= DATEPART(dd, getdate()-1)

Hi, I found a problem with the above code when trying to use it. It doesn't take into account which month or year the date is associated with and only matches the day of month number.

After a bit of research (as I'm no expert) I have come up with these solutions which seem to work....not sure how efficient they are but the certainly get the results:

Previous day - This just converts the datetime to a varchar dd/mm/yyyy and matches the string of getdate()-1 as a varchar:
where convert(varchar ,<datecolumn>,1 03)=convert(var char,getdate()-1,103)



Previous day(s) - This one uses cast and floor to zero out the time. This example returns the previous 3 days:

where (cast(floor(cas t(<datecolumn> as float)) as datetime)>=cast (floor(cast(get date()-3 as float)) as datetime) and cast(floor(cast (<datecolumn> as float)) as datetime)<=cast (floor(cast(get date()-1 as float))

The first getdate()- is how many days how many days you wish to go back and the second one controls the last date in the query. You can increase these values and set results for the last 3 days of the previous week (without testing it I'm guessing you would use 10 and 7).

I'm forever looking for help from forums so thought it was time to give something back.

Hope this helps someone.

[ck9663]

Will this work?

select * from <table> 
where datediff(dd,<date_column>,dateadd(dd,-1,getdate())) = 0

-- CK