Browse DevX
Sign up for e-mail newsletters from DevX


Build Date Generators and Manipulate Date and Time Data in SQL : Page 2

Learn how to build date generators without loops using SQL, and some useful techniques to help you manipulate date and time data.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Create Your Own Date Formats
Even though any programming language offers a lot of date/time formats, you can always face the situation when the format you need is missing. Using SQL, you can easily solve that problem and generate dates or times in your own format. As an example, suppose you require the unusual format of months represented by roman numerals (I've actually met people who used such a format), like this:

31/XII/2006 or 31-XII-2006

To generate dates in this format for a full year, you can use the following code:

SET NOCOUNT ON; IF EXISTS(SELECT * FROM sysobjects WHERE ID = (OBJECT_ID('romanNumbers')) AND xtype = 'U') DROP TABLE romanNumbers; CREATE TABLE romanNumbers( id int IDENTITY(1,1), romNum VARCHAR(4)); INSERT INTO romanNumbers VALUES('I'); INSERT INTO romanNumbers VALUES('II'); INSERT INTO romanNumbers VALUES('III'); INSERT INTO romanNumbers VALUES('IV'); INSERT INTO romanNumbers VALUES('V'); INSERT INTO romanNumbers VALUES('VI'); INSERT INTO romanNumbers VALUES('VII'); INSERT INTO romanNumbers VALUES('VIII'); INSERT INTO romanNumbers VALUES('IX'); INSERT INTO romanNumbers VALUES('X'); INSERT INTO romanNumbers VALUES('XI'); INSERT INTO romanNumbers VALUES('XII'); SELECT CAST(num AS VARCHAR(12)) + '/' + romNum + '/' + '2007' AS MyFormat FROM (SELECT num FROM sequence WHERE num < 32) t1 CROSS JOIN romanNumbers t2 ORDER BY romNum, num;


MyFormat ---------------------- 1/I/2007 2/I/2007 3/I/2007 . . . . . 28/II/2007 29/II/2007 30/II/2007 31/II/2007 . . . . . 28/XII/2007 29/XII/2007 30/XII/2007 31/XII/2007 (372 row(s) affected)

Though the script works, you will find a few unpleasant surprises in the result. First, the query generated 372 days in the year 2007, which is incorrect. Next, each month has 31 days, including February. The reason for such a weird result is obvious: the code took the maximal possible number of days in the month (31) and created the pool of days. Now, you need to build and implement logic that will ensure the proper number of days in the month (year).

Notice that none of the previous examples contained anything about that logic. They used SQL Server built-in date functions that calculate the correct number of days in the month (year) internally, making that process transparent to the programmer. Fortunately, you can use these same functions to generate the correct result in your own date format, as follows:

SELECT MyFormat, YearDay FROM (SELECT id, num, CAST(num AS VARCHAR(12)) + '/' + romNum + '/' + '2007' AS myFormat FROM (SELECT num FROM sequence WHERE num < 32) t1 CROSS JOIN romanNumbers t2) t3 INNER JOIN (SELECT DATEPART(dd,DATEADD(dd, num, 'Dec 31, 2006')) AS DayOfMonth, DATEADD(dd, num, 'Dec 31, 2006') YearDay FROM sequence) t4 ON t3.num = t4.DayOfMonth AND t3.id = DATEPART(mm, t4.YearDay)


MyFormat YearDay ---------------------- ----------------------- 1/I/2007 2007-01-01 00:00:00.000 2/I/2007 2007-01-02 00:00:00.000 . . . . . . . . . . . . . . . . . . . . . . . . 27/II/2007 2007-02-27 00:00:00.000 28/II/2007 2007-02-28 00:00:00.000 1/III/2007 2007-03-01 00:00:00.000 . . . . . . . . . . . . . . . . . . . . . . . . 30/XII/2007 2007-12-30 00:00:00.000 31/XII/2007 2007-12-31 00:00:00.000 (365 row(s) affected)

The method in this section is very flexible and will come in handy when you can't find a suitable built-in date/time format.

Thanks for your registration, follow us on our social networks to keep up-to-date