Getting previous Days Data (1 Viewer)

rkrause

Registered User.
Local time
Today, 05:14
Joined
Sep 7, 2007
Messages
343
09/19/2012


09/20/2012



1/19/2011
1/19/2011
1/19/2011

Yes im in USA
 

SQL_Hell

SQL Server DBA
Local time
Today, 12:14
Joined
Dec 4, 2003
Messages
1,360
Hi again,

Formatdate was not a true date/time value, because of the missing zero at the beginning, I think using Datediff was converting it into date/time for you.

Hopefully the following code is correct:


SELECT *
FROM bk_vAPTData
Where
convert
(datetime,formatdate,101)
BETWEEN convert(varchar(10),dateadd(D,-1,getdate()),101) AND convert(varchar(10),getdate(),101)
 

rkrause

Registered User.
Local time
Today, 05:14
Joined
Sep 7, 2007
Messages
343
That worked thanks.
 

rkrause

Registered User.
Local time
Today, 05:14
Joined
Sep 7, 2007
Messages
343
NM that didnt work. It worked until we started adding records in the table for todays date 9-21-12.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:14
Joined
Aug 30, 2003
Messages
36,118
SQL_Hell may have gone to the pubs across the pond there. If your field truly contains no time component, try

WHERE convert(datetime,formatdate,101) = convert(varchar(10),dateadd(D,-1,getdate()),101)
 

Users who are viewing this thread

Top Bottom