Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    162

    Unanswered: varchar to datetime

    im trying to convert from varchar to datetime. Yes i know its horrible to store date in varchar, but its built this way and im stuck with that.


    This: (column as varchar(24))
    SELECT DateTimeOnAction
    FROM TData

    Gives me: "2010-09-15 13:45:49.809"

    what i dont understand is why this:
    CONVERT(datetime, DateTimeOnAction, 121)

    gives me: "Conversion failed when converting date and/or time from character string."


    thx in advance

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It doesn't:
    Code:
    declare	@DateTimeOnAction varchar(50)
    set	@DateTimeOnAction = '2010-09-15 13:45:49.809'
    select	CONVERT(datetime, @DateTimeOnAction, 121)
    
    --Output
    2010-09-15 13:45:49.810
    
    (1 row(s) affected)
    So you have bad data in your table. Try using the IsDate function to find it:
    Code:
    select * from TData where IsDate(DateTimeOnAction) <> 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Posts
    162
    every single row i returned when i use your suggestion above

    and when i use this:
    Select isdate(DateTimeOnAction)
    from tdata
    where DateTimeOnAction = '2010-09-15 13:45:49.810'

    it also return 0 :s

  4. #4
    Join Date
    Jul 2011
    Posts
    9
    That seems wierd. When I run this:

    SELECT ISDATE('2010-09-15 13:45:49.810')

    I get a 1 to return.

    Jason
    Webmaster at SQL Optimizations School

  5. #5
    Join Date
    Mar 2004
    Posts
    162
    Yes i got the same result, it's à valid dateformat. But when i run isdate against the table it's not valid...

  6. #6
    Join Date
    Sep 2010
    Posts
    153

    it works :O

    it's working guys..

    select convert(datetime, <colname>) from tablename

    it's working..

    where is teh problem?:O

  7. #7
    Join Date
    Mar 2004
    Posts
    162
    i create a table
    CREATE TABLE [dbo].[CounterData_dev](
    [CounterDateTime] [varchar](24) NULL
    ) ON [PRIMARY]

    did an insert from my table to the one above.

    ex. data in my newly created table
    2011-07-05 15:25:34.849
    2011-07-05 15:25:35.809
    2011-07-05 15:25:36.810
    2011-07-05 15:25:37.809
    2011-07-05 15:25:38.814
    2011-07-05 15:25:39.809
    2011-07-05 15:25:40.809
    2011-07-05 15:25:41.809
    2011-07-05 15:25:42.809
    2011-07-05 15:25:43.809
    2011-07-05 15:25:44.809
    2011-07-05 15:25:45.809
    2011-07-05 15:25:46.809
    2011-07-05 15:25:47.809
    2011-07-05 15:25:48.810
    2011-07-05 15:25:49.809
    2011-07-05 15:25:50.809
    2011-07-05 15:25:51.809
    2011-07-05 15:25:52.810
    2011-07-05 15:25:53.809
    2011-07-05 15:25:54.809
    2011-07-05 15:25:55.809
    2011-07-05 15:25:56.809

    None of this rows can be identified as valid datetime value with ISDATE, what on earth is wrong here?
    When i run the above values directly like "select ISDATE('2011-07-05 15:25:34.849')" this returns 1, so why can't the rows in the table be identified as valid datetime value?

  8. #8
    Join Date
    Mar 2004
    Posts
    162
    solved it, the column is one character to big.

    when i remove one character it works and it's identified as a valid datetime: cast(cast(CounterDateTime as varchar(23)) as DATE)

    can someone please explain why ltrim(rtrim(CounterDateTime)) dosen't work?

  9. #9
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Check the following code:

    Code:
    declare @a table (
    	val varchar(24)
    )
    insert into @a values
    	('2011-07-05 15:25:34.849'),
    	('2011-07-05 15:25:34.849 '),
    	(' 2011-07-05 15:25:34.849'),
    	('2011-07-05 15:25:34.849'+CHAR(0))
    
    select ISDATE(val) from @a
    
    select LEN(ltrim(rtrim(val))) from @a
    Only the last one returns 0 (false). My guess is that these dates has been added by an application with a trailing ASCII 0 character (string termination). ASCII 0 characters are not eliminated by ltrim/rtrim.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  10. #10
    Join Date
    Mar 2004
    Posts
    162
    oh nice, thats must be the case.

    Thx for your help.

Posting Permissions

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