March 14, 2016 at 9:59 am
Luis Cazares (3/14/2016)
Jack Corbett (3/14/2016)
macdca (3/14/2016)
"or you need to convert the column to a date without time (no index seeks possible)." - how do I do that?
CONVERT(DATE, ScheduledActivity.ScheduledDateTime)As the number of rows in the table grows your performance will get worse pretty quickly.
Doing this essentially does the same thing as the range query Luis supplied and at least with the range query you have a chance at index seeks. I've got to think that your reporting platform (which is?) has a way to set 2 parameters and for you to manipulate the parameter values so the user only has to supply one.
Converting a datetime column to date, allows index seeks. It's one of those cases that it would happen, so I don't recommend it on a first basis or people will believe it's ok to convert the data types of columns.
I've been working under the assumption that the ScheduledDateTime column is a string column and not a datetime based on the original post leading with:
I have date and time stored in the following format: mm-dd-yyyy hh:mm:ss
SQL Server doesn't store dates in that format and doesn't display date columns in that format by default either.
On a different note, what a bad reporting tool that won't allow to use parameters in different ways.
I've asked at least twice what the reporting tool is because I'm sure it allows it, it is just a matter of education.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 14, 2016 at 10:08 am
Jack Corbett (3/14/2016)
Luis Cazares (3/14/2016)
Jack Corbett (3/14/2016)
macdca (3/14/2016)
"or you need to convert the column to a date without time (no index seeks possible)." - how do I do that?
CONVERT(DATE, ScheduledActivity.ScheduledDateTime)As the number of rows in the table grows your performance will get worse pretty quickly.
Doing this essentially does the same thing as the range query Luis supplied and at least with the range query you have a chance at index seeks. I've got to think that your reporting platform (which is?) has a way to set 2 parameters and for you to manipulate the parameter values so the user only has to supply one.
Converting a datetime column to date, allows index seeks. It's one of those cases that it would happen, so I don't recommend it on a first basis or people will believe it's ok to convert the data types of columns.
I've been working under the assumption that the ScheduledDateTime column is a string column and not a datetime based on the original post leading with:
I have date and time stored in the following format: mm-dd-yyyy hh:mm:ss
SQL Server doesn't store dates in that format and doesn't display date columns in that format by default either.
On a different note, what a bad reporting tool that won't allow to use parameters in different ways.
I've asked at least twice what the reporting tool is because I'm sure it allows it, it is just a matter of education.
I assumed it was a datetime based on the fact that convert(varchar,ScheduledActivity.ScheduledDateTime,103) returned the correct output. With a string, it wouldn't have changed the format.
We need more information to reduce the assumptions. 😀
March 14, 2016 at 10:29 am
macdca (3/14/2016)
I have date and time stored in the following format: mm-dd-yyyy hh:mm:ssI only need the date part, and want to use a data parameter where I specify only one date ie 14/03/2016
So I have used
convert(varchar,ScheduledActivity.ScheduledDateTime,103)
This gives me what I want in the output, but if I search for ScheduledActivity.ScheduledDateTime = 13/04/2016 I get the following error, or no rows returned at all. 'The conversion of a varchar datatype to a datetime datatype resulted in an out of range value'
What should I be doing? It works of I use a between and 2 dates but I only want the user to enter one date into the report parameter.
Thanks
I'm not sure why this has not been mentioned yet since it is a standard construct to get rows between dates.
I would use something like this:
declare @UserDate date = '2016-03-04'
WHERE ScheduledActivity.ScheduledDateTime >= @UserDate
and ScheduledActivity.ScheduledDateTime < DateAdd(dd, 1, @UserDate)
This takes the time element completely out of the equation. Now you can format/convert ScheduledActivity.ScheduledDateTime in the Select statement to your hearts content!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 14, 2016 at 10:37 am
LinksUp (3/14/2016)
I'm not sure why this has not been mentioned yet since it is a standard construct to get rows between dates.
I would use something like this:
declare @UserDate date = '2016-03-04'WHERE ScheduledActivity.ScheduledDateTime >= @UserDate
and ScheduledActivity.ScheduledDateTime < DateAdd(dd, 1, @UserDate)
This takes the time element completely out of the equation. Now you can format/convert ScheduledActivity.ScheduledDateTime in the Select statement to your hearts content!
I did mention it. The OP didn't like it.
March 14, 2016 at 10:54 am
Luis Cazares (3/14/2016)
I did mention it. The OP didn't like it.
You are right! I blew past it on the first page.
I'm not sure I understand his objection. If he can use tsql statements to do conversions he should be able to use tsql for the construct you provided.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply