Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    Jun 2005
    Posts
    115

    Unanswered: conversion from CHAR to DATETIME error

    on a column DateNew = DateTime

    i am trying :
    INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003/01/31 10:04:14')

    and i get an error :
    conversion of char data type to datetime data type resulted in an out of range datetime value

    I had never this error before , do you know why ?
    i must enter a yyyy/mm/dd format because this database will be used for Fr and Us langages

    thank you for helping
    Last edited by quentin; 02-14-06 at 05:19.

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Smile

    Im not getting any error with ur insert statement.I think u are passing date value as a variable which is in char.

    try trim that variable on both side using ltrim,rtrim before inserting.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    88
    lookup SET DATEFORMAT in Books online. Maybe that would help.

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    You can try this -
    insert <tablename>
    select convert(datetime,'2003/01/31 10:04:14')

    Well, for more info check this out....
    http://groups.google.co.in/group/mic...9fae5346ac4bc0
    Last edited by rudra; 02-14-06 at 07:01.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this instead --

    INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003-01-31 10:04:14')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003/01/31 10:04:14')
    insert <tablename>
    select convert(datetime,'2003/01/31 10:04:14')

    or this one

    try this instead --

    INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003-01-31 10:04:14')
    __________________

    These statements all are working fine in my machine also,so no problem in statements...I think mailler made a point ..plz check that.
    Joydeep

  7. #7
    Join Date
    Jun 2005
    Posts
    115
    i got it with
    INSERT INTO [dbo].[Users] (DateNew) VALUES (convert(datetime,'2003/01/31 10:04:14',111))

    thank you

  8. #8
    Join Date
    Jun 2005
    Posts
    115
    i got it with convert(datetime,'2003/01/31 10:04:14',111)

    thank you

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I know that I'm being pendantic here, but I'd invest a bit of time now into making your application much more portable/flexible/etc. The ISO 8601 format for date/time information is CCYY-MM-DD HH:MMS.TTT and that format is used by virtually the entire computing universe. It has been adopted by W3C which means that almost anywhere you find time on the Internet, you'll find it in this format.

    You'll almost certainly save yourself lots of time and energy if you switch to using this format now, instead of having to switch to it later!

    As a side note, if you expect your application to grow to the point where you may need to support more than one server, I'd suggest you spend the time to convert your application to use UCT (aka GMT) now too... This is easy to do up front, and almost impossible to do "after the fact" due to many very difficult problems caused by different locales.

    -PatP

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Pat Phelan
    ....due to many very difficult problems caused by different locales...
    PatP
    You mean time zones, right?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    You mean time zones, right?
    You can think of the problem that way, but it is really more complex than just time zone... A locale rolls the problem up into a nice tidy (but not simple bundle). Time zones reflect a difference between local time and UCT, essentially a time offset. The problem comes from Daylight Savings Time, where different locales observe different shift dates, not all of which adjust by the same amount (some only move 30 minutes).

    Unravelling the mess is easy if done while recording an event because it is easy for a computer to find UCT from its local time if necessary. Once the time is stored, there may not be any way to recover true UCT again. This gets really hard to explain, but there have been a couple of good whitepapers done on the problem.

    -PatP

  12. #12
    Join Date
    Jun 2005
    Posts
    115
    Thanks a lot I shall do it at once but HOW do you convert a normal date into ISO 8601 ?

    what is the SQL command for it ?

  13. #13
    Join Date
    Jun 2005
    Posts
    115
    for the moment I store all my dates time inthe format
    yyyy/MM/dd hh:mm:ss

    2000/12/31 18:50:06
    I have just to replace / by - ?

    thanks a lot

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by quentin
    for the moment I store all my dates time inthe format
    yyyy/MM/dd hh:mm:ss

    2000/12/31 18:50:06
    I have just to replace / by - ?

    thanks a lot
    Yes! Exactly.

    This is a relatively small change "up front", but it makes your date/time format match the format used by nearly everything else. That makes your code much easier to port to other programming languages, databases, etc at a later time. It is a small investment up front, that can pay off hugely in the future.

    As a side note, SQL Server stores the data internally in a completely different form... Once you get the data into a column or variable, the work has been done. The only place you need to change anything is in the actual conversion from a character representation to a DATETIME.

    -PatP
    Last edited by Pat Phelan; 02-15-06 at 01:54.

  15. #15
    Join Date
    Jun 2005
    Posts
    115
    Pat I did it , I have inserted 100 rows in my SQL database
    in the good format but the database seems change it for the french format
    31/12/2005 18:20:45

    Once you get the data into a column or variable
    I store the date in a datetime format column ?!

    thanks a lot

    and for searching any row in my database where a date
    >
    <
    =
    <>
    =<
    <=
    to another date but on the date not on the datetime (yyyy-MM-dd) ?

Posting Permissions

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