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.
10 24870
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.
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
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.
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.
Will this work? - select * from <table>
-
where datediff(dd,<date_column>,dateadd(dd,-1,getdate())) = 0
-
-- CK
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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',...
|
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...
|
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...
|
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...
|
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...
|
by: pai |
last post by:
db_TBO db_TBT
-----------------------------
--------------------------------------------------------
TBOID | Date TBTID | TBOID | Date...
|
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...
|
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
|
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 ...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
|
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...
| |