Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2007
    Posts
    208

    how to convert date string to datetime

    I have a table in which a date value is stored as varchar.

    some of these values are stored ina dd/mm/yyyy format and other values are stored in a yyyy-mm-dd format

    Now I wish to retrieve some data by querying between two dates. However I need to convert the varchar date value to datetime in order to do this but since the date value is in two different formats, the following doesn't work.

    select date_value
    from my_table
    where CONVERT(DATETIME, date_value, 103) between @date1 and @date2


    How can you convert the date value to datetime when its stored in mutiple formats. I can't change the table itself as I dont have admin privelages.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,922
    Drop the format value from your convert statement, let it figure out the format.

    This points out that you have a bigger problem, in that the dates are tempermental when stored in character form. You might want to as your DBA to convert these into a DATE column!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2007
    Posts
    208
    Quote Originally Posted by Pat Phelan View Post
    Drop the format value from your convert statement, let it figure out the format.

    This points out that you have a bigger problem, in that the dates are tempermental when stored in character form. You might want to as your DBA to convert these into a DATE column!

    -PatP
    i get out of range error if I drop the convert function because the @date1 and @date2 values are passed in dd/mm/yyyy format?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,445
    personally
    Id create a new column that is of datetime datatype
    update that new column with the value of the old column converting as required, use a where clause to differentiate the ISO dates from non iso dates
    onbce you are certain that all date values have been updated
    then drop the old column and rename the new column waht ever the old column was called

    once its done I'd grasp the developer/DBA/whoever warmly by the throat and suggest they learn how DB's work before inflicting such horrors on a 'real' db. If they are unable to see the error of their ways then consider taking 'em out for a necklace party

    ...what me a fundalemntalist
    sounds a bit extreme. An effective use of a necklace stops them repeating this sort of error
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2007
    Posts
    208
    Quote Originally Posted by healdem View Post
    personally
    Id create a new column that is of datetime datatype
    update that new column with the value of the old column converting as required, use a where clause to differentiate the ISO dates from non iso dates
    onbce you are certain that all date values have been updated
    then drop the old column and rename the new column waht ever the old column was called

    once its done I'd grasp the developer/DBA/whoever warmly by the throat and suggest they learn how DB's work before inflicting such horrors on a 'real' db. If they are unable to see the error of their ways then consider taking 'em out for a necklace party

    ...what me a fundalemntalist
    sounds a bit extreme. An effective use of a necklace stops them repeating this sort of error
    this is returning me back to the orginal question. How do I convert to datetime when date value is stored in two different formats? copying to a new table or converting within a query is the same problem.

  6. #6
    Join Date
    Jan 2013
    Location
    Woodland Hills, CA
    Posts
    18

    Converting varchar dates when formats vary

    /**
    / Purpose: Convert dates stored as varchar format to datetime value.
    / Author: YesAgile
    / Date: 2013.01.08
    /
    / Notes: Works only if formats are the following:
    /
    / yyyy-mm-dd
    / dd/mm/yyyy
    */
    alter function dbo.fn_convertDate(@dt varchar(10), @formatCode int = 120)
    returns datetime
    begin
    declare
    @ret datetime -- Return value.
    ,@dtCopy varchar(10) -- Copy used for parsing.
    ,@dtAsInt int -- @dtCopy as int.

    -- Set default date value to return.
    set @ret = convert(datetime, '01/01/1900')

    -- Strip the '/' and '-' from varchar date value.
    set @dtCopy = replace(replace(@dt, '/', ''), '-', '')

    -- Convert to int.
    set @dtAsInt = convert(int, @dtCopy)

    -- Is integer value a date?
    -- Note that SQL dates are stored internally as numerical values
    -- so passing an int to the isdate() is still valid.
    if (isdate(@dtAsInt) = 1)
    begin
    set @ret = convert(datetime, @dt, 120)
    end
    else
    begin
    set @ret = convert(datetime, @dt, 103)
    end


    return @ret
    end
    go

    select dbo.fn_convertDate('2012-12-22', default)
    select dbo.fn_convertDate('22/12/2012', default)

  7. #7
    Join Date
    Jan 2013
    Location
    Woodland Hills, CA
    Posts
    18

    how to convert date string to datetime

    I agree with ozzi. The DBA group needs to have better discipline and store dates instead of strings. Please note that when using a function (UDF or system-built-in) in a SQL update, select or delete statement you are not getting the optimal performance from SQL Server.

    The reason is that the function is being called for each row that qualifies and/or for every row in your WHERE clause to see if it qualifies. This has the affect of turning your query or proc into a row-by-row process instead of set based.

    Hope the function works for you.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    Dropping the format value ("103") from your function call should work, provided your dates are consistently in these two formats. SQL Server will implicitly convert both formats.

    Code:
    with TestDates as
    (select	'01/08/2012' as TestDate
    union select '2012-01-13')
    select	TestDate,
    	CONVERT(DATETIME, TestDate) as RealDate
    from	TestDates
    TestDate RealDate
    ---------- -----------------------
    01/08/2012 2012-01-08 00:00:00.000
    2012-01-13 2012-01-13 00:00:00.000
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Mar 2007
    Posts
    208
    Quote Originally Posted by blindman View Post
    Dropping the format value ("103") from your function call should work, provided your dates are consistently in these two formats. SQL Server will implicitly convert both formats.

    Code:
    with TestDates as
    (select	'01/08/2012' as TestDate
    union select '2012-01-13')
    select	TestDate,
    	CONVERT(DATETIME, TestDate) as RealDate
    from	TestDates
    TestDate RealDate
    ---------- -----------------------
    01/08/2012 2012-01-08 00:00:00.000
    2012-01-13 2012-01-13 00:00:00.000
    this doesn't work because the parameters @date1 and @date2 are entered in SSRS in the following format dd/mm/yyyy

    When I run a report that uses the following query:

    select date_value
    from my_table
    where CONVERT(DATETIME, date_value) between @date1 and @date2

    I get error in SSRS stating date value out of range.

    When I run the same query by itself in SQL server management studio and replace the parameter values with hardcoded values as follows it still doesnt work.

    select date_value
    from my_table
    where CONVERT(DATETIME, date_value) between '09/01/2013' and '10/01/2013'

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,445
    fix the problem at source
    doping this bastsardisation every time you run the query is folly
    get the data right first
    fix what is causing the problem then its not an ongoing problem over time.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,922
    Quote Originally Posted by ozzii View Post
    this doesn't work because the parameters @date1 and @date2 are entered in SSRS in the following format dd/mm/yyyy
    Sure it does... Make them DATE instead of VARCHAR and you can enter them however you please (assuming your locale is set correctly)!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Mar 2007
    Posts
    208
    Quote Originally Posted by blindman View Post
    Dropping the format value ("103") from your function call should work, provided your dates are consistently in these two formats. SQL Server will implicitly convert both formats.

    Code:
    with TestDates as
    (select	'01/08/2012' as TestDate
    union select '2012-01-13')
    select	TestDate,
    	CONVERT(DATETIME, TestDate) as RealDate
    from	TestDates
    TestDate RealDate
    ---------- -----------------------
    01/08/2012 2012-01-08 00:00:00.000
    2012-01-13 2012-01-13 00:00:00.000

    dropping the format option from the convert function causes an out of range date error when the date is in the following format dd/mm/yyyy and where dd is greater than 12 i.e. 13/06/2012

    In addtion when date is in the format dd/mm/yyyy the convert function swaps the dd and mm around so I end up with incorrect date i.e.

    12/06/2012 ends up as 2012-12-06

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    Quote Originally Posted by ozzii View Post
    dropping the format option from the convert function causes an out of range date error when the date is in the following format dd/mm/yyyy and where dd is greater than 12 i.e. 13/06/2012
    I'm guessing here, but I bet that's because there is no 13th month.
    Thirty days hath September, April June and November....well, I can't remember the rest, but google it to be sure. If there is a 13th month, then my company has been cheating me out of some paychecks I deserve.
    If you have messy data, then you have messy data. Clean up the data or write a custom function like YesAgile suggested.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,445
    doesn't sound like messy date, just data that isn't doesn't use the US format

    something the OP pointed out in post #1
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Mar 2007
    Posts
    208
    Quote Originally Posted by blindman View Post
    I'm guessing here, but I bet that's because there is no 13th month.
    Thirty days hath September, April June and November....well, I can't remember the rest, but google it to be sure. If there is a 13th month, then my company has been cheating me out of some paychecks I deserve.
    If you have messy data, then you have messy data. Clean up the data or write a custom function like YesAgile suggested.
    The 13/06/2012 which uses the dd/mm/yyyy format (british format) translates as the 13th of June 2012 - how is that a messy date????

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •