• Replies 4 replies
  • Subscribers 337 subscribers
  • Views 23 views
  • Users 0 members are here
Related

joining date and time

Richard Laslett
$organization

 

 

 

 

 

I have a several hundred thousand records

 

in a table where the date and time are seperate

values

that I need

 

to join into one datetime value

where

 

 

(perf_dt) = 2004-12-20 00:00:

00.000

and

 

 

perf_time = 1899-12-30 18:30:

00.000

result that

 

is desired is 2004-12-20 18:30:

00.000

I would appreciate a tip

 

from some

one how to code this

  • Former Member
    Former Member $organization
    Hi Richard
    I would:
    + Convert each of them to char values in a controlled format.
    + Chop off the date substring of the first one, and the time substring of the second.
    + Concatenate the two into one string.
    + And convert that string back to a datetime
    Kind of clunky, but should work.

    Ken McSwain
    +61 (0)418 659 360
    Sent from BlackBerry
    Please consider the environment before printing this email.
    =====This message is intended for the addressee(s) named and may contain confidential information.
    If you are not the intended recipient, please delete it and notify the sender.
    Views expressed in this email are those of the individual sender and are not necessarily the views of the Sydney Opera House Trust=====
  • $organization

    Hi Richard

    I guess you are saying you have a perf_dt column and perf_time column in the same table?

    If so, give this statement a whirl...

    select convert(datetime,convert(varchar,perf_dt,101) + ' ' + convert(varchar,perf_time,108),111) from your_table

    Thanks, Ben

  • $organization in reply to Former Member
    Thanks Ken.
    I went with a solution from Ben Magson

    select convert(datetime,convert(varchar,perf_dt,101) + ' ' + convert(varchar,perf_time,108),111) from your_table

    which worked well.
    Thanks anyway for your contribution.

    -----Original Message-----
    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ken McSwain
    Sent: Friday, 14 August 2009 5:50 PM
    To: Richard Laslett
    Subject: Re: [Tessitura Technical Forum] joining date and time

    Hi Richard
    I would:
    + Convert each of them to char values in a controlled format.
    + Chop off the date substring of the first one, and the time substring of the second.
    + Concatenate the two into one string.
    + And convert that string back to a datetime
    Kind of clunky, but should work.

    Ken McSwain
    +61 (0)418 659 360
    Sent from BlackBerry
    Please consider the environment before printing this email.
    =====This message is intended for the addressee(s) named and may contain confidential information.
    If you are not the intended recipient, please delete it and notify the sender.
    Views expressed in this email are those of the individual sender and are not necessarily the views of the Sydney Opera House Trust=====


    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums.

    In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

    Elevation | The Australian Ballet in 2009
    Explore our website
    ________________________________________________
    DISCLAIMER: PLEASE NOTE This email is confidential to the intended recipient.
    If you receive this email in error please advise by return mail. You should not copy,
    use or disclose this email or its contents. Information may be subject to change
    without notice. See www.australianballet.com.au for our privacy policy.

    Please consider the environment before printing this email