handling odd date format

  • Hi,

    I need to handle date format "41981". Please help!

    Abhijit - http://abhijitmore.wordpress.com

  • Abhijit More (8/21/2015)


    Hi,

    I need to handle date format "41981". Please help!

    So what date does 41981 represent?

    A. April 1981 (M/YYYY)

    B. April 19, 1981 (M/DD/YY or M/D/YYYY)

    C. December 8, 2014 (Excel integer for a date)

    D. Something else

    You have to give us something to go on here.

    If this is a column in a table, I hope your goal is to convert it into an actual date data type. Storing dates properly lets you use date math and prevents invalid data (like 12/34/2015) from getting into the table.

  • guessing.....

    declare @d as datetime = '1900-01-01'

    select dateadd (dd,41981,@d) as calcdate

    calcdate

    2014-12-10 00:00:00.000

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Good guess.

    --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)

  • Yes, a very good guess. That would be the simplest one to handle, so let's hope for that one. 😉

    So, the list is now:

    A. April 1981 (M/YYYY)

    B. April 19, 1981 (M/DD/YY or M/D/YYYY)

    C. December 8, 2014 (Excel integer for a date)

    D. The number of days since 01/01/1900

    E. Something else

  • There's no good way to handle this unless you know the month, date, and year that are being entered. If I enter 19122011

    Is this

    Nov 20, 1912 or Dec 19, 1912?

    Surely your application will let you guess, but it's possible to get confused. Really you want your application to validate that someone enters a month/day/year where you expect, but that can still cause issues. For example.

    08042015

    I enter that in my vacation system. It is seen at April 8, 2015, but I meant for it to be Aug 4, 2015. That's because we use different cultural date formatting mechanisms.

    Once you know the month/day/year, you can CAST or CONVERT this to a date.

  • So, Abhijit More. Did any of this work for you or not? This IS a 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)

  • Yes, I'd like to see if one of the guesses was right or if it was something else.

  • Hi Abhijit,

    Please try this,

    Select CAST(41981 as datetime)

    Output

    2014-12-10 00:00:00

    Thanks,

    Srikanth s

  • Those are always a pain...no guarantee the INT is based on days since 1900-01-01. I once had to export data from a 'very' old legacy system which the date was an INT based on days since 1938-06-30. Go figure... :hehe:


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • OOOOOOHHHHHH!!!!! I GET IT!!! 😉 Now I know why the OP hasn't responded! He doesn't really need help. It was a ploy for him to post a link to blog out anyone accusing him of SPAM.

    --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 11 posts - 1 through 10 (of 10 total)

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