Some handy

examples of working with datetime

Date / Time data types are probably amongst the most used data ones. Based on the Microsoft definition, date and time data types are for representing date and time of day. Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

There’s a great deal of sources you can use for information on the datetime data type but I will not focusing myself on the many aspects of datetime data type such as design, performance, search, conversions and so on. Those aspects discussed by the other articles and authors many times. In this article I would like to provide some handy examples of the usage and share with you some tricks when using the datetime data type.

Let’s consider a case when we need to format values stored in datetime columns . In many cases (especially for the data transfer processes) the user will specify the output format. For example, our users prefer dates to be displayed as 2 digits day, 2 digits month, i.e. ‘01/09/2004’. Usually after the conversion this date displayed as ‘1/9/2004’.As you can see the difference is to be able not suppress leading zeroes in a given field.

A simple trick to achieve this effect is to make your output independent from length of the actual value and to always return the necessary number of characters.

declare @cust table (customer_id int, customer_nm varchar(50))
insert into @cust (customer_id,customer_nm) values (11111,'TestNM 1')
insert into @cust (customer_id,customer_nm) values (6511111,'TestNM 1')
insert into @cust (customer_id,customer_nm) values (92311111,'TestNM 1')
select 
   Right('0000000000' + Cast(customer_id as varchar),10) as customer_id 
   , customer_nm
  from @cust

customer_id customer_nm
----------- -------------------------------------------------- 
0000011111  TestNM 1
0006511111  TestNM 1
0092311111  TestNM 1

The same trick can be used for the datetime data

select right( '00' + Cast(datepart(dd, getdate()) as varchar), 2)
select right( ''00' + Cast(datepart(mm, getdate()) as varchar), 2)

Here are some examples of using datetime data type. A transaction/modification very often requires some unique identifier. In light-load transactional systems (1-2 transactions per second) you can produce a unique id based on the timing characteristics of the transaction itself.. I use this method for years and have no problems. Theoretically it is possible that two modifications will get the same id, but the chance of it happening is not significant and can be mitigated with appropriate exception handling.

CREATE FUNCTION dbo.UDF_ID (@dt datetime) 
  Returns bigint 
as
Begin 
declare @modid bigint
select @modid = cast(year(@dt) as char(4))+ right(('0' +
       cast(month(@dt) as varchar(3) )),2) +
       right(('0' + cast( DATEPART(dd,@dt) as varchar(3))),2) +
       right(('0' + cast( DATEPART(hh,@dt) as varchar(3))),2) +
       right(('0' + cast( DATEPART(mi,@dt) as varchar(3))),2) +
       right(('0' + cast( DATEPART(ss,@dt) as varchar(3))),2) +
       right(('00' + cast( DATEPART(ms,@dt) as varchar(6))),3)
return @modid
End

There are many clients in the company I am working for. And each client required different date format for the reports and data transfers. You can facilitate the presentation of date-time value in different formats by utilizing a user defined function accepting two parameters: date-time value itself and a format type variable. For instance, if such a function called UDF_CONVERTDATE, you invoke it by:

select dbcentral.dbo.UDF_CONVERTDATE (getdate(),6)

The source code for the function is in file UDF_CONVERTDATE_A.txt. For the sake of clarity the source code contains logic for only a few formats and some other parts are omitted.

Sometimes it is required to use a UTC date instead of your company’s customary format. The next code fragment shows a user defined function that converts the saved time into UTC time.

CREATE FUNCTION dbo.UDF_UTCDATE (@dt datetime, @now datetime, @utcnow datetime) 
  Returns datetime as
Begin 
declare @utcdate datetime
select @utcdate = DATEADD(ss, DATEDIFF(ss, @now, @utcnow) , @dt )
return @utcdate
End

There are three parameters to this function. The second and the third are always the same, and the first one is your datetime field or variable. The second one is always getdate(), and the third one is always getutcdate(). Parameter 2 and 3 are required to be able to bypass the user defined function restrictions stating that non-deterministic functions can’t be used inside of the user defined function.

Select dbcentral.dbo.udf_utcdate(order_dt, getdate(), getutcdate() ), order_id
 From customer_orders
 Where customer_id = 10

The next function has been developed to determine previous, closest, or next date from the given

date. The source is in UDF_CNP_DAYOFWEEK_A.txt.

As always, some features are omitted but the code is fully functional and can be used as is. It has four parameters:

@datevar date calculation from

@day_flag - P - previous; N - next; C - closest

@dayofweek - Monday, Tuesday .... The closest, next, or previous in relation of the passed date @datevar

@curday - if 0 and current day same as @dayofweek then closest, next and previous is the current date. Otherwise the date will be calculated

For example to choose the next Friday:

select dbcentral.dbo.UDF_CNP_DAYOFWEEK (getdate(),'N', 'Friday', 1) as NextFriday

To choose the previous Monday:

select dbcentral.dbo.UDF_CNP_DAYOFWEEK (getdate(),'N', 'Monday', 1) as PreviousMonday

Conclusion

There are many ways to use datetime data type. This article illustrated some of them without venturing into a discussion on their performance characteristics, ability to produce unique values or any other aspects, which may be subjects of other publications.