Date Parameter

Question

Post reply

Date Parameter

DarthBurrito

SSCommitted

Points: 1697
More actions
May 11, 2011 at 1:28 pm

#239515

Need some assistance here. I have a report that has a Date parameter. This is only a single date parameter, not a range. When I run the report for a specific day, I dont get results, which I know I should. I found out why this is happening. In the database, the dates are in this format:
'2007-10-30 14:50:13.000'
The report, I assume is searching for '2007-10-30 00:00:00.000' on that exact hour. If I'm wrong, please correct me.
If I'm right, how can I get the report to return results no matter what time on '2007-10-30'?
Thanks for any assistance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

phonetictalk Hall of Fame Points: 3582 More actions · Answer 1

You can use the following to convert the date column to the date with a time of midnight:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS DateOnly

(Replace GETDATE() with the name of your date column)

If you google, there are a lot of solutions, including some that convert the date to a string, then truncate the string and convert it back, but my understanding is that that is not so performance-friendly as using this option (the datediff calculates the number of days between day 0 and the current date - the date add then adds that number to day 0, resulting in today's date without a time).

If you always need to filter on date only like this, it's better to store the date without the time in the first place (if you have any control over that).

Leonard
Madison, WI

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Check out the folowing links:

http://www.sql-server-helper.com/tips/date-formats.aspx

http://technet.microsoft.com/en-us/library/ms187819.aspx

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 3

LM1 (5/11/2011)
You can use the following to convert the date column to the date with a time of midnight:
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS DateOnly
(Replace GETDATE() with the name of your date column)
If you google, there are a lot of solutions, including some that convert the date to a string, then truncate the string and convert it back, but my understanding is that that is not so performance-friendly as using this option (the datediff calculates the number of days between day 0 and the current date - the date add then adds that number to day 0, resulting in today's date without a time).
If you always need to filter on date only like this, it's better to store the date without the time in the first place (if you have any control over that).

This option will not allow SQL Server to use an index on the date column. The better option is to use an open-interval range check. The first step is to make sure the input date does not have any time portion, then we use greater than or equal and less than:

SET @inputDateParameter = dateadd(day, datediff(day, 0, @inputDateParameter), 0);

SELECT ...

FROM dbo.YourTable

WHERE DateColumn >= @inputDateParameter

AND DateColumn < dateadd(day, 1, @inputDateParameter);

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

DarthBurrito SSCommitted Points: 1697 More actions · Answer 4

Jeffrey Williams-493691 (5/15/2011)
LM1 (5/11/2011)
You can use the following to convert the date column to the date with a time of midnight:
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS DateOnly
(Replace GETDATE() with the name of your date column)
If you google, there are a lot of solutions, including some that convert the date to a string, then truncate the string and convert it back, but my understanding is that that is not so performance-friendly as using this option (the datediff calculates the number of days between day 0 and the current date - the date add then adds that number to day 0, resulting in today's date without a time).
If you always need to filter on date only like this, it's better to store the date without the time in the first place (if you have any control over that).
This option will not allow SQL Server to use an index on the date column. The better option is to use an open-interval range check. The first step is to make sure the input date does not have any time portion, then we use greater than or equal and less than:
SET @inputDateParameter = dateadd(day, datediff(day, 0, @inputDateParameter), 0);
SELECT ...
FROM dbo.YourTable
WHERE DateColumn >= @inputDateParameter
AND DateColumn < dateadd(day, 1, @inputDateParameter);

Thanks, I actually used the this method before this was posted and it worked. I asked a friend of mine and he suggested it. Thanks

phonetictalk Hall of Fame Points: 3582 More actions · Answer 5

Didn't think about the index implications - thanks Jeffrey for the better solution 😀

Leonard
Madison, WI