t-sql datetime conversion issue

  • In a sql server 2012 database, I have a column called TransactionPaymentDate. The column is defined

    as varchar(50). The value in the column looks like '05012014'. I need to compare the value of this field

    with data fields that use datetime or smalldatetime.

    I have tried the following sql statements so I can convert the value to datetime or smalldatetime:

    1. CONVERT(smalldatetime,substring(TransactionPaymentDate,5,4) + '-' + substring(TransactionPaymentDate,1,2)+ '-' + substring(TransactionPaymentDate,3,2))

    **this gives an error message: The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

    2. TransactionPaymentDate = cast(substring(TransactionPaymentDate,5,4) + '-' + substring(TransactionPaymentDate,1,2)+ '-' + substring(TransactionPaymentDate,3,2) as smalldatetime

    This does not work in a where statement.

    I want to use to be able to do the following type of a compdarsion:

    DECLARE @startdate smalldatetime = '01012014'

    DECLARE @enddate smalldatetime = '03192014'

    Select * from cust_table

    where TransactionPaymentDate between @startdate and @enddate.

    I try the following sql, and I have problems:

    Select * from cust_table

    where convert(smalldatetime,substring(TransactionPaymentDate,5,4) + '-' + substring(TransactionPaymentDate,1,2)+ '-' + substring(TransactionPaymentDate,3,2))

    between @startdate and @enddate

    Thus can you show me sql that will work for this date comparison I want to do?

  • It sounds like you think the date you are getting is formatted MMDDYYYY, but is really formatted DDMMYYYY. Either that or local settings are tripping you up.

    The actual example you gave is ambiguous and your statement converts it fine.

    So I'd first analyze the data and verify the format (which is the month). Also, I'd specify the format code in the convert statement. There is a list of them here. You can add it as the third argument, and then you can make sure that the DB knows how you want to translate the string.

  • got the sql to work

  • Just a note, in my experience, it seems easier to convert (or cast) the string to date in the YYYYMMDD format as there is no separator involved and this works across collations. If I remember correctly, SQL Server stores all dates in this format internally.

  • murdakillemall (3/18/2014)


    Just a note, in my experience, it seems easier to convert (or cast) the string to date in the YYYYMMDD format as there is no separator involved and this works across collations. If I remember correctly, SQL Server stores all dates in this format internally.

    Not quite. SQL Server stores dates as integers for the number of days since 1st January 1900, with a negative value if the date is before this base date. DateTime is two 4-byte integers, one for the number of days and the other for the time.

    A good article by Kalen Delaney on this - http://sqlmag.com/sql-server/solving-datetime-mystery.

    And if I haven't bored you enough with this:

    According to MS YYYYMMDD is part of the ISO 8601 standard for date formatting and so is the a way of entering a recognisable date, but it will be stored as a 4-byte integer.

    There is also some dispute as to whether the MS docuumentation is correct in repect to this:

    https://connect.microsoft.com/SQLServer/feedback/details/814796/try-parse-does-not-parse-iso-8601-yyyymmdd-international-date-format

  • Now that you have figured out how to convert your character data to a datetime I would HIGHLY recommend you change the datatype so you don't have to wrestle with this anymore.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • wendy elizabeth (3/17/2014)


    got the sql to work

    So, what did you do to get it to work? Two way street here. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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