Post reply

using converted var char date time field to return records in a date

  • March 14, 2016 at 9:59 am

    #1864984

    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

    #1864987

    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. 😀

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 14, 2016 at 10:29 am

    #1864993

    macdca (3/14/2016)

    I have date and time stored in the following format: mm-dd-yyyy hh:mm:ss

    I 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

    #1864996

    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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 14, 2016 at 10:54 am

    #1865006

    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