Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Working with Datetime

By Leo Peysakhovich, (first published: 2004/12/22)

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.

Total article views: 29022 | Views in the last 30 days: -15
 
Related Articles
FORUM

Help with converting varchar to datetime

Convert varchar to datetime

FORUM

ASP Date and Time to SQL Datetime

Datetime Conversion from varchar

FORUM

Pass datetime variable to query as varchar

convert varchar to datetime sql server 2008

FORUM

Convert 6 character varchar to datetime

How can I convert a 6 character string (varchar) to a datetime?

FORUM

DateTime Problem

Datetime

Tags
advanced querying    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones