Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jomypgeorge
Starting Member
31 Posts |
 Posted - 2010-11-29 : 04:11:20
|
| Hi friends,i need to set a time period for for a column in a table.table have a field [created_time] which is smalldatetime type.i have to restrict this value, for example between 10:00 Am to 9:00 Pm. this time period is dynamic. How can i store this settings (start_time like 10:00 Am and end_time like 9:00 Pm). i found smalldatetime data type for this. but it stores date too. but in my case date is not relevant.how can i solve this?Thanks in advance..... |
|
|
vaibhavktiwari83
Aged Yak Warrior
843 Posts |
Posted - 2010-11-29 : 04:19:41
|
| In SQL Server 2005 There is not any datatype for time only.and if you want to restrict values for time period.and its dynamic then you need to do it at your front end or in Stored procedure.If its fixed then you can use Check constraint for the same.Vaibhav TIf I cant go back, I want to go fast... |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-29 : 04:22:27
|
| You can store the time in a character column but that's not a good idea as you will have to convert everytime you want to do arithmetic on it.Another option is to ues the smalldtetime datatype and set the date to 1 jan 1900 (this is stored as 0 but could really be any date).This will mean that you are storing the time and can sum minutes - just have to format the data for display but you would have to do that anyway.You could put a constraint on the column to ensure that the the date is 0 if you wish.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Jomypgeorge
Starting Member
31 Posts |
Posted - 2010-11-29 : 06:25:54
|
| thanks for your replaysi saved the time settings as smalldatetime fieldon checking i convert the date part to 1/1/1900 and compared that with given date. given date also converted to 1/1/1900.convertion is done by DATEADD(dd,-DATEDIFF(dd,0,@dtpDate),@dtpDate ) |
|
|
|
|
|
|
smalldatetime issue