In this article I will explain how to store Date and Time in SQL Server 2005 using a User Defined Type.
In this article I will explain how to store Date and Time in SQL Server 2005 using a User Defined Type.IntroductionIn SQL Server 2008 four new data types were added. Two of them are Date and Time for storing date and time as separate data types. But if you need to deal with separate Date and Time in SQL Server 2005, either you store Date in DateTime and parse its date and time part or store date and time in a string data type.An alternative is to create a User Defined Data Type (UDT) for storing Date and Time. So here we will create two UDTs and then we will use these User Defined Data Types in table columns. Then we will add a CHECK CONSTRAINT to the columns to ensure only valid data is inserted in these columns.Step 1: Create User Defined Data TypesCREATE TYPE DateFROM VARCHAR(10) NOT NULLCREATE TYPE TimeFROM VARCHAR(8) NOT NULLHere we have created two User Defined Data Types for date and time based on VARCHAR data type.Step 2: Create Table
Create a table named "Student" and define its "DateOfBirth" and "TimeOfBirth" columns with the Date and Time UDTs respectively.Step 3: Add Functions for validationCREATE FUNCTION ValidateDate(@Date VARCHAR(10))RETURNS INTEGERASBEGIN DECLARE @ReturnValue INT DECLARE @DD VARCHAR(2) DECLARE @MM VARCHAR(2) DECLARE @YYYY VARCHAR(4) IF(@Date LIKE'[0-3][0-9]/[0-1][0-9]/[0-9][0-9][0-9][0-9]') BEGIN SET @DD=SUBSTRING(@Date, 1, 2) SET @MM=SUBSTRING(@Date, 4, 2) SET @YYYY=SUBSTRING(@Date, 7, 4) SET @ReturnValue=ISDATE(@MM+'/'+@DD+'/'+@YYYY) END ELSE SET @ReturnValue=0 RETURN @ReturnValueEND
CREATE FUNCTION ValidateTime(@Time VARCHAR(8))RETURNS INTEGERAS BEGIN DECLARE @ReturnValue INTEGER DECLARE @HH INT DECLARE @MM INT DECLARE @SS INT
IF(@Time LIKE'[0-9][0-9]:[0-9][0-9]:[0-9][0-9]') BEGIN SET @HH=CAST(SUBSTRING(@Time, 1, 2) AS INT) SET @MM=CAST(SUBSTRING(@Time, 4, 2) AS INT) SET @SS=CAST(SUBSTRING(@Time, 7, 2) AS INT)
IF((@HH BETWEEN 0 AND 23)AND (@MM BETWEEN 0 AND 59) AND(@SS BETWEEN 0 AND 59)) BEGIN SET @ReturnValue=1 END ELSE BEGIN SET @ReturnValue=0 END END ELSE SET @ReturnValue=0
RETURN @ReturnValueENDHere we have added two functions ValidateDate and ValidateTime for validating Date and Time. Date accepts data in the (dd/mm/yyyy) format and Time accepts data in the (hh:mm:ss) format.Step 4: Add Constraint to the ColumnsALTER TABLE StudentWITH NOCHECK -- OptionalADD CONSTRAINT chkDate CHECK(dbo.ValidateDate(DateOfBirth)=1)
ALTER TABLE Student WITH NOCHECK -- OptionalADD CONSTRAINT chkTime CHECK(dbo.ValidateTime(TimeOfBirth)=1)Here we have added two CHECK CONSTRAINTs, "chkDate" and "chkTime" to the DateOfBirth and TimeOfBirth columns respectively for validating data inserted in these columns.If you try to insert data in DateOfBirth and TimeOfBirth columns in a format other than specified, you will get an error stating "Conflicted with CHECK constraint".Happy Programming!
SQL Queries For Beginners