Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Friday 13th datetime bug!

Options
  • 13-03-2009 9:50pm
    #1
    Darragh29
    Closed Accounts Posts: 7,097 ✭✭✭


    I didn't think I'd be having this issue on Friday 13th but here goes...!

    http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/2e36331d-0081-47e4-a0f4-fe3183547187/

    Basically due to how the US format their date using MMDDYY and we use DDMMYY, when I'm passing a datetime into my MS 2005 Server DB, the DB is expecting the date to be in the MMDDYY format, but the data I'm trying to post is in the DDMMYY format.

    This works grand until the 13th day of the month, then you get this error:

    Conversion failed when converting datetime from character string.

    It is weird that this should happen on Friday the 13th, maybe this is where the day gets its fame from!

    Just wondering has anyone fixed this using c#?


Comments

  • Options
    #2
    stevire
    Registered Users Posts: 1,512 ✭✭✭stevire


    You sure your using the correct SQL statement when calling/entering dates?

    Should be along the lines of:

    Convert(datetime, '" & Date & "',103)


  • Options
    #3
    democrates
    Closed Accounts Posts: 2,046 ✭✭✭democrates


    Darragh29 wrote: »
    I didn't think I'd be having this issue on Friday 13th but here goes...!

    http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/2e36331d-0081-47e4-a0f4-fe3183547187/

    Basically due to how the US format their date using MMDDYY and we use DDMMYY, when I'm passing a datetime into my MS 2005 Server DB, the DB is expecting the date to be in the MMDDYY format, but the data I'm trying to post is in the DDMMYY format.

    This works grand until the 13th day of the month, then you get this error:

    Conversion failed when converting datetime from character string.

    It is weird that this should happen on Friday the 13th, maybe this is where the day gets its fame from!

    Just wondering has anyone fixed this using c#?
    You're saying passing in the non-us format is working grand but as the 13th shows it's taking your dayofmonth as month and visa versa - that means existing dates in the db are incorrect and you'll need to run an update to correct them, backing up first of course.

    It's ages since I worked on MS but I've a vague recollection that the server locale may be involved in getting your db set up correctly, but research it first a check everything on the server if you make a core change like that, eg scheduled tasks.

    If you have to leave the db and server as is then you can either cast the 'string' to the correct date format in sql, or write two functions in your app - one to change to db format, one to change back to appspace format.


Advertisement