Tuesday, March 27, 2012

Create a table with a field with only Time datatype

Hello experts,
I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?Hello experts,
I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?

See this enigma's post link (http://sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk)
Its a good one ...|||i dont understand why you are not thinking in terms of a datetime/smalldatetime field by ignoring the date part and whats wrong in it. however, if you are rigid you can store it either as char field or as numeric field (float/decimal). remember you do not actually save anything by doing so.

storing as char field is straight forward. if you want to store as number (remember that each date+time has a numeric representation) can take hints from the following queries

--1 minute as number
select cast(cast('20060101 11:01 AM' as smalldatetime) as float)- cast(cast('20060101 11:00 AM' as smalldatetime) as float)
--it returns approx 0.0006944

--use that to get a date time 20060101 11:00 AM back
select cast( cast(cast('20060101' as smalldatetime) as float) + (0.0006944*60*11) as smalldatetime)

quite unnecessary...;-)sql

No comments:

Post a Comment