Previous day query

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_data_time_change where mvs_creation_date = {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.Riaan,
Try
select * from mvs_store_all_data_time_change
where mvs_creation_date > (getdate()-1)|||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|||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 :)|||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.|||Hi Phil. Ok I treid your suggestion, but get the following error "Syntax error converting datetime from character string.". Any more ideas :).

Cheers, Riaan.|||

Quote:

Originally Posted by 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.

Ho Riaan,

try this one...

select * from mvs_store_all_data_time_change
where convert(char,mvs_creation_date,102) = convert(char,getdate()-1,102)

regards,
Pankaj Bhatnagar|||

Quote:

Originally Posted by bhatnagarp

Ho Riaan,

try this one...

select * from mvs_store_all_data_time_change
where convert(char,mvs_creation_date,102) = convert(char,getdate()-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.|||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.