Monday, 7 April 2008

Date Only and Time Only data types in SQL Server 2005

Dear friends many times we require datatype that gives date only or time only values. Here's how to do it in SQL Server 2005 ?


First, please create two user defined data types:

create type Date from dateTime
create type Time from dateTime

So, internally , these types are really just DateTime. But, we will apply some rules to these types so that the Date data type will always be constrained to a time exactly at midnight, and the the Time data type will always be at the "base date", or 1900-01-01:


create rule DateOnlyRule as
dateAdd(dd,datediff(dd,0,@DateTime),0) = @DateTime

go

create rule TimeOnlyRule as
datediff(dd,0,@DateTime) = 0

go

Finally, we call sp_bindrule to bind the rules to the data types:


EXEC sp_bindrule 'DateOnlyRule', 'Date'
EXEC sp_bindrule 'TimeOnlyRule', 'Time'


And its done ! Now, we can create a table that uses our brand-new data types:


create table MyTable
(
EmpID int identity primary key,
TotalSalary money not null,
SalDate Date not null,
SalTime Time not null
)

Notice that SalDate and SalTime are two separate columns here. If we try to insert data, our rules will ensure that our SalDate and SalTime columns only contain the appropriate data, and by doing so we can simply calculate SalDate + SalTime to get the Salary DateTime.

The following inserts will succeed:


insert into MyTable(TotalSalary , SalDate , SalTime )
values (250, '2008-05-01', '04:00:00 AM')

insert into MyTable(TotalSalary , SalDate , SalTime )
values (490, '2008-05-03', '3:40:30 PM')


But this will fail, since our rules are violated (getdate() returns the current date and the time):


insert into MyTable(TotalSalary , SalDate , SalTime )
values (200, getdate(), getdate())



Msg 513, Level 16, State 0, Line 1
A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'MyDatabase', table 'dbo.MyTable, column 'SalDate'.
The statement has been terminated.


SQL 2008 does finally implement true Date and Time data types, but until then, I hope this gives you some ideas to play with.

kick it on DotNetKicks.com

2 comments:

  1. hello~
    I have the same problem. But I'm using MS Access. I would like to display the records on GridView.
    What can I do?

    ReplyDelete
  2. I have take web user control of Date picker in the control i have select date and Use the sqlserver database so the selected date not insert into the database tadle

    ReplyDelete