Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27

    Unanswered: date conversion question

    i have 2 fields that i want to concatenate and then convert it's date format. one field is a smalldatetime, the other is a varchar:

    u_Departure_Time = varchar(5), ex data is 05:30 or 16:30
    EffFrom = smalldatetime, ex data is 09/01/2003

    trying to do something like this:
    CONVERT(datetime,(dateadd(dd, value, EffFrom)+' '+u_Departure_Time),'mm/dd/yyyy hh:mi:ms AM')

    i need to take the varchar 24hr time from u_Departure_Time, concatenate it to EffFrom and make it 12hr format. In the above examples I'd like to obtain:

    9/1/2003 5:30:00 AM
    9/1/2003 4:30:00 PM

    it seems basic, but i'm an oracle guy, just started using sql server.

    thanks!

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    select cast(convert(char(10), EffFrom, 101) + ' ' + u_Departure_Time as datetime)

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: date conversion question

    Try this one:

    declare @date smalldatetime,@time varchar(10)
    set @date='09/17/2003'
    set @time='16:30'
    select cast(@date+' '+@time as datetime)

  4. #4
    Join Date
    Jul 2003
    Location
    DC Metro
    Posts
    27
    Originally posted by ms_sql_dba
    select cast(convert(char(10), EffFrom, 101) + ' ' + u_Departure_Time as datetime)
    cool, i tried this:
    ,cast(convert(char(10), EffFrom, 101) + ' ' + u_Departure_Time as smalldatetime)

    but it's in 24hr time,

    sample: 2003-08-01 19:25:00

    i need it to match another tables format of dd/mm/yyyy h:mm:ss AM/PM

    like: 2003-08-01 7:25:00 PM

    this is really a newbie question, but what does the '101' signify?
    Last edited by aglio412; 09-19-03 at 15:22.

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    101 converts 2003-09-01 to 09/01/2003 format so that you can att time portion to it before converting it back to datetime.

Posting Permissions

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