Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2006
    Posts
    10

    Unanswered: SQL Date Format Issue

    I'm having a problem with inserting a date into a table. For example, if I wanted to insert today (and its not always today so no now() alowerd) into a table I do this:

    insert into TableA (TheDate) Values ('18-12-06')

    Now this is Australian date so 18th of December 2006.

    It gives me an error:

    Msg 242, Level 16, State 3, Line 2
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
    The statement has been terminated.

    But if I change the values around:

    insert into TableA (TheDate) Values ('12-18-06')

    It works.

    But my code is passed to it in the first type. So how do I get SQL to accept it?

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the "italian" style 5 in a CONVERT expression

    insert into TableA (TheDate) Values (convert(datetime,'18-12-06',5))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2006
    Posts
    10
    Thanks r937. That works nicely.

    However, this may be a question for another topic, but I'll try my luck anyway...

    I'm using VB.Net as the front end. Creating it in VS 2005.

    I've got three text boxes and am assigning them to variables:

    Code:
    Dim id As String = Me.cboID.Text
    Dim eventDate As Date = Me.cboEventDate.Text
    Dim eventTime As Date = Me.txtEventTime.Text
    I then set up all the connection and that works nicely. However, when I try and execute the insert command, it gives me an error.

    Code:
    Insert into TableA Values ('" & id & "', convert(datetime,'" & EventDate & "',5), '" & EventTime & "');
    Error:

    Conversion failed when converting datetime form character string.

    Any ideas?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have no idea, but i'm gonna guess you have a problem with the time value

    say, why do you have separate date and time columns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    Even when you decide to keep the date and time seperate (not practical, but it is possible) try the same trick as R937 told you, but now using:
    convert(datetime,'hh:mm:ss', 8)

    When you look into the column afterwarts, you'll see that the date is set to 1-1-1900 in the time column ... but you probably already noticed that.

    Gr,
    Yveau

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


  6. #6
    Join Date
    Dec 2006
    Posts
    10
    Ok. I've been playing with the time format and found an alternative.

    If I set the format by using:

    Code:
    Format(TheDate, "MM/dd/yyyy")
    Then it works. I believe it doesn't like the dd/mm/yyyy so the american style works.

    As to having two separate date and time fields... good question. I was originally using postgreSQL but found that MSSQL's datetime actually stores both, so I may just use the one.

    Thanks for your help everyone.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What about doing your display formatting at the application layer, which is actually displaying the data?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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