Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Posts
    74

    Unanswered: Date Conversion in SQL Server

    Hi folks,

    Here are the fields I have

    eventDate = smallDateTime 2005-12-12 00:00:00
    eventTime = varchar(20) 1:00:00
    newEventDate = dateTime [desired result: 2005-12-12 1:00:00]

    When I run the following script:
    update healthEvent
    set newEventDate = cast(substring(convert(varchar,eventDate,120),1,10 )+' '+eventTime as DateTime)

    I get "Syntax error converting datetime from character string." error.

    Any ideas why?

    Thanks!

    -Parul

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    -- Comment
    What in the name of all that is relational and logical would somebody design a POS like this?

    -- Answer
    Because you are not putting them together properly.

    The date string at 1 AM is like this:
    2007-05-17 01:00:00.000

    The date string at 1 PM is like this:
    2007-05-17 13:00:00.000

    You are trying to slam the 1 against the space after the date without the leading zero.

    Start from the inside and work out. When I convert, I like to explicitly define the size of my char variable inside of the convert instead of letting a varchar guess at what i want (convert(char(10),eventDate,120).

    In this case I would use an 11 char convert to pick up the space after the date portion plus a 0 plus the ltrim(rtrim(time)) component.

    Code:
    declare @eventDate smallDateTime 
    select @eventDate = '2005-12-12 00:00:00'
    declare @eventTime varchar(20) 
    select @eventTime = '1:00:00'
    declare @newEventDate dateTime --[desired result: 2005-12-12 1:00:00]
    set @newEventDate = cast(substring(convert(varchar,@eventDate,120),1,10 )+' 0'+@eventTime as DateTime)
    select @newEventDate
    --  Results
    ------------------------------------------------------ 
    2005-12-12 01:00:00.000
    (1 row(s) affected)

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Oct 2002
    Posts
    74
    Thanks so much for your reply.

    Unfortunatley, I am still getting the same error as I try to insert @newEventDate into another date field.

  4. #4
    Join Date
    Oct 2002
    Posts
    74
    Plus do I need that leading zero at all times? What if I have @eventTime as '11:00:00'

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    You will have to do a CASE statement for your time segment on the datalength of the ltrim(rtrim(time segment)) . If len = 5 add the zero else don't.

    -- This is all just a Figment of my Imagination --

Posting Permissions

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