February 12, 2008 at 9:05 am
How can I select a date with the following format: '2007-01-13' when the date field is of a smalldatetime format in SQL 2000 or SQL 2005 SSRS?
February 12, 2008 at 9:36 am
Look up CONVERT in Books Online. There should be an appropriate style for that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2008 at 9:57 am
Thanks for the reply.
In SSRS report #1, we are able to prompt the user with the businessdate with the following when businessdate data type is datetime and the user enters in 02-02-2008 when prompted:
select sum(t.tendermediatotal)TenderedCash,t.BusinessDate,r.name,r.RevenueCenterID from TENDER_MEDIA_DAILY_TOTAL t join revenue_center r on r.revenuecenterid=t.revenuecenterid
where t.tenderMediaID = 146745 and t.businessdate=(@BusinessDate) group by t.tendermediatotal, r.revenuecenterid, t.businessdate,r.name
In SSRS report #2, no records are being returned with the following when the user enters the following in the prompt for a smalldatetime data type: 02-02-2008
SELECT CONVERT(DATETIME,CONVERT(CHAR(10),sysdate,110)) as BusinessDate, productname, grossamount
FROM BSTBatch
WHERE VatLevel = 519 and sysdate= @businessdate
Records will return in report #2 if the user enters in the following in the prompt: 02-02-2008 00:00:00 and the operand for sysdate is changed to sysdate> @businessdate. We would like the user to enter in '02-02-2008' format for sysdate(smalldatetime) and the operand to reflect sysdate= @businessdate
February 12, 2008 at 11:05 pm
I'm guessing the sysdate has a time with it? Datetimes always have a time portion that must be taken into account when comparing
You've got 2 options.
You can use a function on the date column to strip the time off and do an equality. This is easier to write, bay may performa badly, as the function on the date will prevent index seeks
You can write the query with an inequality like you mentioned. The query looks more complex, but this approach does allow index seeks, if there's an index on that column.
Examples:
Option 1
SELECT ...
FROM BSTBatch
WHERE VatLevel = 519 and DATEADD(dd,DATEDIFF(dd,0,sysdate),0) = @businessdate
Option 2
SELECT ...
FROM BSTBatch
WHERE VatLevel = 519 and sysdate >= @businessdate AND sysdate < DATEADD(dd,1,@businessdate)
Both, when you pass in 02-02-2008 will get you all rows that have a sysdate sometime in the 2nd Feb 2008
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply