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

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;

Results:

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)

Results:

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.

Previous Page

Next Page