Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Posts
    20

    Unanswered: saperate the columns

    Hello guys,
    I have a time stamp in my table and I want to convert into two separate columns Date and time.

    I have something like this 06/01/2009 00:00:00 and I want to make it like

    Column 1 (Date) --> 06/01/2009
    column2( Time) --> 00:00:00



    can anybody help??

    Kunal

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT CONVERT(VARCHAR(10),timestamp,101) AS "Date"
         , CONVERT(VARCHAR(8),timestamp,108) AS "Time"
      FROM daTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    20
    HI Rudy,
    Thank you very much for your help but I was wondering if there is any way that I can ALTER my table permanently in a way that instead of one column stamp,I can have two permanent columns Date and Time.

    I will really appreciate your guidance.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    separate DATE and TIME columns are possible in SQL Server 2008

    in earlier versions your only choice is DATETIME

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2009
    Posts
    20
    I have SQL Server 2008. How can do it?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by kunaaldesai
    I have SQL Server 2008. How can do it?
    use ALTER TABLE to add the two new columns, then UPDATE the table to set the DATE and TIME column values from the original DATETIME column, then ALTER TABLE again to drop the DATETIME column

    may i ask why you want separate columns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2009
    Posts
    20
    Thank you very much Rudy. i tried that and it is working very fine. I am really thankful to you.

    Kunal

Posting Permissions

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