Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Friday 13th datetime bug!

  • 13-03-2009 08: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

  • #2
    stevire
    Registered Users, Registered Users 2 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)


  • #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