Getting previous Days Data (1 Viewer)

[rkrause]

I want to be able to have a query automattically get the previous days data. i dont want to have the user enter any dates.
heres my query:
SELECT*--,MIN(formatdate)
FROM bk_vAPTData
Where formatdate >=dateadd("D",-1,getdate())

this gives me data for both 9-19-12 and 9-20-12
how can i just get my records from 9-19-12

 

[boblarson]

You have >= where it should just be =

 

[rkrause]

if i do this: i get no results

SELECT *--,MIN(formatdate)
FROM bk_vAPTData
Where formatdate = dateadd("D", -1, getdate())

 

[boblarson]

if i do this: i get no results

SELECT*--,MIN(formatdate)
FROM bk_vAPTData
Where formatdate =dateadd("D",-1,getdate())

Does your date field have a time element included?

Also, what is this getdate procedure?

 

[rkrause]

No time included
im not sure what getdate() is, im pretty sure its a built on function for todays date?

 

[pr2-eugin]

To get today's date you use Date() function..

 

[boblarson]

im not sure what getdate() is, im pretty sure its a built on function for todays date?

Go find out. You can use todays date by using Date(), so you may not need that function. But go to the VBA window, click in the code somewhere and then click CTRL+F to bring up the find dialog and put in getdate and select ENTIRE PROJECT from the selections available on the dialog and then click find. It should take you to it but if you have code which uses it, it may go to that instance(s) first. So, if it does, just right click on that instance of its name and select DEFINITION and it should take you right to it.

 

[pbaldy]

Also, what is this getdate procedure?

You must have had a brain cramp. GETDATE() is SQL Server's equivalent to Now(). This thread is in the SQL Server forum, but the syntax seems mixed, so perhaps it needs to be clarified where this is running?

 

[rkrause]

Im using SSMS for my queries, im not familiar with VBA in SSMS?
if i use DATE() i get a error saying its not a recognized built-in function.

 

[boblarson]

Sorry, I missed that you were in the SQL Server category.

So, getdate there returns a date in the yyyy-mm-dd format and you delimit it with quotes. Also, SQL Server date/time fields always include time (if you don't use them they just store as 00:00:00.000. But I think you can use:

SELECT*--,MIN(formatdate)
FROM bk_vAPTData
Where formatdate >dateadd("D",-1,getdate()) AND <getdate()

 

[boblarson]

You must have had a brain cramp. GETDATE() is SQL Server's equivalent to Now(). This thread is in the SQL Server forum, but the syntax seems mixed, so perhaps it needs to be clarified where this is running?

Yep, I got here using the New Posts link and sometimes I miss which category those are posted in.

 

[rkrause]

i get this error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '<'.

 

[boblarson]

Sorry, my bad (not thinking too well this morning):

SELECT*--,MIN(formatdate)
FROM bk_vAPTData
Where formatdate >dateadd("D",-1,getdate()) AND formatdate <getdate()

 

[SQL_Hell]

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]*[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] bk_vAPTData[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Where[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] formatdate [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]BETWEEN [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]convert[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]10[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]dateadd[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]D[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],-[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]1[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]getdate[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]()),[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]101[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]) [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]AND [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]convert[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]10[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]),[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]getdate[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080](),[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]101[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
[/COLOR][/SIZE]

 

[SQL_Hell]

Just for completeness

Another way to combat the time issue, is to use a date column rather than a date/time column (sql 2008 and above)

 

[rkrause]

Neither of the last 2 posts got me what i need.
Boblarson i still only get 9-20-12 data and not yesterdays 9-19-12

and SQL_hell i dont get any results from you.

 

[rkrause]

i was able to find this and it seems to work. not sure whats different than ive already tried.

SELECT *--,MIN(formatdate)
FROM bk_vAPTData
WHERE formatdate>=DATEADD(d,DATEDIFF(d,0,GETDATE())-1,0) AND
formatdate<DATEADD(d,DATEDIFF(d,0,GETDATE())+0,0)

 

[SQL_Hell]

that makes no sense, mine should have worked fine. Your datediff function does nothing. Maybe fomatdate isn't a true date/time value

 

[rkrause]

Im puzzled as well...Im not sure, it should be date/time field.

 

[SQL_Hell]

I am sure we can get to the bottom of this, please post the results of the following queries:

select convert(varchar(10),dateadd(D,-1,getdate()),101)

select convert(varchar(10),getdate(),101)

select top 3 formatdate
from bk_vAPTData

Are you in USA?