Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Time data type and convertion to

Date and time Tips and tricks

Time data type and convertion to

by  djj55  Posted    (Edited  )
A new data type is TIME (time [(fractional second precision)]) it "Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock." (see BOL http://msdn.microsoft.com/en-us/library/bb677243.aspx)

The new help Microsoft has for the CAST/CONVERT does not go into detail about TIME and I wanted to have a time column so I did this simple test
Code: 
DECLARE @mydatetime AS DATETIME = GETDATE()
SELECT @mydatetime 
    , CAST(CONVERT(VARCHAR, @mydatetime, 108) AS TIME) AS MTime
    , CAST(CONVERT(VARCHAR, @mydatetime, 108) AS TIME(0)) AS MTime0
    , CAST(CONVERT(VARCHAR, @mydatetime, 108) AS TIME(1)) AS MTime1
    , CAST(CONVERT(VARCHAR, @mydatetime, 108) AS TIME(2)) AS MTime2
    , CAST(CONVERT(VARCHAR, @mydatetime, 108) AS TIME(3)) AS MTime3
    , CAST(CONVERT(VARCHAR, @mydatetime, 108) AS TIME(4)) AS MTime4
    , CAST(CONVERT(VARCHAR, @mydatetime, 108) AS TIME(5)) AS MTime5
    , CAST(CONVERT(VARCHAR, @mydatetime, 108) AS TIME(6)) AS MTime6
    , CAST(CONVERT(VARCHAR, @mydatetime, 108) AS TIME(7)) AS MTime7
    , CAST(@mydatetime AS TIME) AS STime
    , CAST(@mydatetime AS TIME(0)) AS STime0
    , CAST(@mydatetime AS TIME(1)) AS STime1
    , CAST(@mydatetime AS TIME(2)) AS STime2
    , CAST(@mydatetime AS TIME(3)) AS STime3
    , CAST(@mydatetime AS TIME(4)) AS STime4
    , CAST(@mydatetime AS TIME(5)) AS STime5
    , CAST(@mydatetime AS TIME(6)) AS STime6
    , CAST(@mydatetime AS TIME(7)) AS STime7
The tests shows that you can just use the CAST as time from a datetime value. It also shows that a text string can be used.

Notice the fractional second precision values and the results.

Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top