SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Working with DATE, TIME, TIMESTAMP in MS SQL!

    Hi everyone!

    I am working with a MSSQL 2000 database.

    I am using PHP to display the table data. The table format (simplified) is as follows:

    timekey, name, last_updated.

    The timekey is in the following format:

    2006031306595835900003

    I believe this to be in the MSSQL TIMESTAMP format.

    The last_updated column is in the following format:

    2006-03-13 06:59:58.000

    What I want to do is seperate the Time and Date from either one of these columns (at the database source) into seperate columns in their own right.

    Thus allowing sorting of the table on Date or Time... i.e.

    SELECT name, date, time FROM table ORDER BY date.

    Anyone have any ideas? CAST or CONVERT? Im really stuck on this one..

    Thanks in advance

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I have managed to do this using:

    SELECT convert(varchar, last_updated, 103) AS date, convert(varchar, last_updated, 8) AS time FROM table

    Is this the best way to do it? Correct? Should I be converting to VARCHAR?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by drj201 View Post
    Should I be converting to VARCHAR?
    that's what you are doing

    CONVERT(VARCHAR, last_updated, 103)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry. I mean... is it correct to convert to VARCHAR? I have done some research and it seems there is no DATE only type in MSSQL. Only a DATETIME. If there was a date I could have converted it to DATE. This would allow me to use ORDER BY and get a correct date sort and not as is the case with VARCHAR an alphabetical search.

    Thanks for your help!

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    if you have a DATETIME column, and you want it to be in date sequence, just sort it (using ORDER BY)

    if you have a DATETIME column, and you want it to be in time sequence (e.g. if you want all times sorted together no matter which day), then ORDER BY CONVERT(VARCHAR,last_updated,8)

    this works because style 8 is hh:mm:ss, i.e. from largest unit to smallest
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,633
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Use DATETIME and have the front-end application handle the formatting.

    TIMESTAMP in MSSQL has nothing to do with PHP/MySql/Unix timestamps. It is really for row versioning.


Bookmarks

Posting Permissions

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