473,320 Members | 1,940 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Previous day query

6
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.
Aug 2 '06 #1
10 24870
Riaan,
Try
select * from mvs_store_all_data_time_change
where mvs_creation_date > (getdate()-1)
Aug 2 '06 #2
lipsa
35
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
Aug 3 '06 #3
lipsa
35
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 :)
Aug 3 '06 #4
Riaan
6
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.
Aug 7 '06 #5
Riaan
6
Hi Phil. Ok I treid your suggestion, but get the following error "Syntax error converting datetime from character string.". Any more ideas :).

Cheers, Riaan.
Aug 7 '06 #6
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
Aug 8 '06 #7
Riaan
6
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.
Aug 10 '06 #8
Riaan
6
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.
Aug 11 '06 #9
Lymedo
1
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>,103)=convert(varchar, 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(cast(<datecolumn> as float)) as datetime)>=cast(floor(cast(getdate()-3 as float)) as datetime) and cast(floor(cast(<datecolumn> as float)) as datetime)<=cast(floor(cast(getdate()-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.
Sep 2 '08 #10
ck9663
2,878 Expert 2GB
Will this work?

Expand|Select|Wrap|Line Numbers
  1. select * from <table> 
  2. where datediff(dd,<date_column>,dateadd(dd,-1,getdate())) = 0
  3.  
-- CK
Sep 2 '08 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
4
by: sah | last post by:
I need some help with the following query: DECLARE @SRV VARCHAR(20), @date smalldatetime SET @SRV = (select @@servername) SET @date = '20040901' select Srv_Name = @SRV, DB_Name = 'DB_NAME',...
6
by: Martin Lacoste | last post by:
Ok, before I headbutt the computer... don't know why when I add criteria in a query, I get an 'invalid procedure call'. I also don't know why after searching the help in access, the various access...
19
by: bdt513 | last post by:
I am trying to extract the values from a query using VBA. Specifically, I want to concatenate all the values of the "rosEmail" field from query "qselRosterEmailList" into one string (strEmails). I...
6
by: tizmagik | last post by:
I am having a lot of difficulty generating a CrossTab Query based report. I have looked online for several tutorials and whatnot but I have not been able to really find what I'm looking for, nor...
1
by: AJ | last post by:
Folllowing on from a previous post, i have created a stored query as follows. SELECT c.ID, c.Company_Name, p., 1 As QueryNbr FROM (Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID) LEFT...
1
by: pai | last post by:
db_TBO db_TBT ----------------------------- -------------------------------------------------------- TBOID | Date TBTID | TBOID | Date...
29
by: wizofaus | last post by:
I previously posted about a problem where it seemed that changing the case of the word "BY" in a SELECT query was causing it to run much much faster. Now I've hit the same thing again, where...
1
by: mrobinsc | last post by:
** This SQL statement returns 4 rows SELECT COUNT(*) G.ACTIVITY_ID G.RESOURCE_TYPE G.RESOURCE_CATEGORY G.RESOURCE_SUB_CAT G.ANALYSIS_TYPE G.PROJECT_ID
4
by: Mangala Teli | last post by:
I have table as Table1 p_cd dt qty price amt 001 12/13/2009 10 20 200 002 12/14/2009 5 10 50 001 12/15/2009 10 20 200 001 12/19/2009 8 20 160 i want result as p_cd ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.