June 30, 2008 at 9:01 am
Hi All,
I'm a newbie, trying to learn the ropes.
I need to add code that will allow me to create a subscription using yesterdays date. This report needs to run using yesterday's date, which is then emailed. The code below prompts the user for the date. How can I change it to do the above?
Select u.Usetime, a.AccessCode, a.Name, u.VisualID
From Usage u inner join AccessCodes a
On u.AccessCode = a.AccessCode
where (a.AccessCode < '28')
AND (
(u.VisualID Like '%251______%')
(u.Usetime > @rdate)
(u.Usetime < @rdate + 1)
)
Order By u.VisualID ASC
Thanks for all you help!
June 30, 2008 at 9:55 am
I answered this last Friday (Post #525356) when you posted it under "Date Parameter (Post #525310)."
Here it is again:
The following will give you yesterday's date:
In T-SQL
DATEADD(dd,-1,GETDATE())
In VB/SSRS
=DATEADD("d",-1,NOW())
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
June 30, 2008 at 9:57 am
I looked all over for that. Thanks again.
June 30, 2008 at 1:35 pm
This isn't producing any results:
Select u.Usetime, a.AccessCode, a.Name, u.VisualID
From Usage u inner join AccessCodes a
On u.AccessCode = a.AccessCode
where (a.AccessCode < '28') AND (u.VisualID Like '%251______%') And (u.Usetime = DATEADD(dd,-1,GETDATE()))
Order By u.VisualID ASC
June 30, 2008 at 2:33 pm
Couple things:
Is your usetime field a datetime field? If so, the values are going to include time along with date so using = without modifying both GETDATE() and usetime is going to limit what gets returned.
For example, at this moment GETDATE() = 2008-06-30 15:47:47.043
so DATEADD(dd,-1,GETDATE()) = 2008-06-29 15:47:47.043. Using = will only return records with u.usetime exactly = 2008-06-29 15:47:47.043.
What you'll want to do is strip off the time. One way to do it is convert both from datetime to varchar like this:
Select u.Usetime, a.AccessCode, a.Name, u.VisualID
From Usage u inner join AccessCodes a
On u.AccessCode = a.AccessCode
where (a.AccessCode < '28') AND (u.VisualID Like '%251______%')
And (CONVERT(VARCHAR(10),u.Usetime,101) = CONVERT(VARCHAR(10),DATEADD(dd,-1,GETDATE()),101))
Order By u.VisualID ASC
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a87d0850-c670-4720-9ad5-6f5a22343ea8.htm explains the CONVERT function.
If usetime is a datetime but stored without time being specified
i.e. 2008-06-29 00:00:00.000, you shouldn't have to convert it but you'll still need to strip the time from DATEADD(dd,-1,GETDATE()). For example,
Select u.Usetime, a.AccessCode, a.Name, u.VisualID
From Usage u inner join AccessCodes a
On u.AccessCode = a.AccessCode
where (a.AccessCode < '28') AND (u.VisualID Like '%251______%')
And (u.Usetime = CONVERT(DATETIME,CONVERT(VARCHAR(10),DATEADD(dd,-1,GETDATE()),101)))
Order By u.VisualID ASC
The inner convert strips out the time and the outer one changes it back to datetime. Theoretically, the convert back to datetime shouldn't be necessary as SQL should do the convert implicitly but my experience is sometimes it does, sometimes it doesn't.
If usetime is not a datetime, you'll only need to strip the time from DATEADD(dd,-1,GETDATE()) by converting it to VARCHAR or CHAR, whichever best matches usetime's datatype.
There are probably less verbose methods to do this but this how I learned to do it and I'm too old to change . If anybody's got better, jump on in.
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply