How to Store Date and Time in Separate Columns in SQL Server 2005

Introduction

In this article, I will explain how to store Date and Time in SQL Server using a User Defined Type.

In SQL Server 2008, four new data types were added. Two are Date and Time for storing date and time as separate data types. But if you need to deal with a separate Date and Time in SQL Server 2005, either store Date in DateTime and parse its date and time part or store the 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 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 Types

CREATE TYPE Date
FROM VARCHAR(10) NOT NULL

CREATE TYPE Time
FROM VARCHAR(8) NOT NULL

Here we have created two User Defined Data Types based on the VARCHAR data type for date and time.

Step 2. Create Table

SQL.gif

Create a table named "Student" and define its "DateOfBirth" and "TimeOfBirth" columns with the Date and Time UDTs, respectively.

Step 3. Add Functions for validation

CREATE FUNCTION ValidateDate(@Date VARCHAR(10))
RETURNS INTEGER

AS

BEGIN

      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 @ReturnValue
END

CREATE FUNCTION ValidateTime(@Time VARCHAR(8))
RETURNS INTEGER

AS
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 @ReturnValue

END

We have added two functions, ValidateDate and ValidateTime, for validating Date and Time. Date accepts data in the (dd/mm/yyyy) format, and time takes data in the (hh:mm:ss) format.

Step 4. Add Constraints to the Columns

ALTER TABLE Student
WITH NOCHECK -- Optional

ADD CONSTRAINT chkDate CHECK(dbo.ValidateDate(DateOfBirth)=1)

ALTER TABLE Student
WITH NOCHECK -- Optional

ADD CONSTRAINT chkTime CHECK(dbo.ValidateTime(TimeOfBirth)=1)

Here we have added two CHECK CONSTRAINTs, "chkDate" and "chkTime," to the DateOfBirth and TimeOfBirth columns to validate the 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!


Similar Articles