Date type in RDBMS.

Introduction.

In this article I want to show how to manipulate date data type in Microsoft SQL Server and Oracle database. Most applications require to store and manipulate dates and times, and date manipulation is sometimes complicated not only are they highly formatted data, but also there are a lot of rules to do valid processing through a lot of functions. You can find a lot of developers with a lot of question closely related to the date data type. Thus, the purpose of this article is to help the reader gain an understanding of this interesting data type.

Date and time in SQL Server.

Before SQL Server 2008, in SQL Server we don't have a separate data types for date and times. Dates can be stored in SQL Server 2000/2005 in datetime or smalldate. Datetime data type can store dates from January 1, 1753 to December 31, 9999 with a precision up to 0.003 fraction of a second. The smalldate data type can store dates from January 1, 1900 to June 6, 2079 with a precision of a second. When you enter only the time part, then the base date is added. When you enter the date part, then the time is appended as midnight (see Listing 1).

create table tbDate

( col datetime);

go

insert into tbDate values('8:00 AM');

go

insert into tbDate values('March 24,2008');
go

Listing 1.

There are styles to format the input and output when converting from datetime into characters. We must use the convert function and the following list of styles (see Table 1).

Style ID	Style Type
0 or 100	mon dd yyyy hh:miAM (or PM)
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mm:ss
9 or 109	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	mm-dd-yy
111	yy/mm/dd
112	Yymmdd
13 or 113	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
20 or 120	yyyy-mm-dd hh:mi:ss(24h)
21 or 121	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130	dd mon yyyy hh:mi:ss:mmmAM
131	dd/mm/yy hh:mi:ss:mmmAM

Table 1.

For example, if you wan to select the date part of the datetime value (see Listing 2).

select convert(varchar,col,101) from tbDate;

go

Listing 2.

And if you want to select the time part of the datetime value (see Listing 3).

select convert(varchar,col,108) from tbDate;

go

Listing 3.

You can insert datetime values using the same approach. For example if you want to insert a date using the ISO format (yyyymmdd) (see Listing 4). It's remarkable to say that this style always work independently of your computer settings (not affected by SET DATEFORMAT or SET LANGUAGE).

insert into tbDate values(convert(datetime,'20080320',112));

go

Listing 4.

In SQL Server 2008,now we have a new DATE data type to store only the date without the time (see Listing 5).

create table tbNewDate

(col date);

go

insert into tbNewDate values('3/24/2008');

go

Listing 5.

You can query for datetime values the same way for any data type in SQL Server (using operators like =, <>, >,>=,<,<=, LIKE, BETWEEN) (see Listing 6).

select *

from tbDate

where col='20080324';

Listing 6.

Using the LIKE operator states that you can search for a partial date or time (see Listing 7).

select *

from tbDate

where col like '%2008%';

Listing 7.

In SQL Server we can find a lot of functions used to work with date and times.

GETDATE and CURRENT_TIMESTAMP functions return the current date and time. GETUTCDATE returns the Greenwich Mean Time (Universal Time Coordinate) (see Listing 8).

select getutcdate() as utc,getdate() as local;

Listing 8.

DATEADD allows incrementing and decrementing a datetime value. The syntax is DATEADD(datepart,number,date).
The datepart codes are described in the table 2.

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	Hh
minute	mi, n
second	ss, s
millisecond	ms

Table 2.

Let's illustrate this function with an example (see Listing 9).

declare @now datetime;

set @now = getdate();

select @now as now,dateadd(hh,5,@now) as five_hours_later;

Listing 9.

Let's remove 5 hours to the current time (see Listing 10).

declare @now datetime;

set @now = getdate();

select @now as now,dateadd(hh,-5,@now) as five_hours_before;

Listing 10.

The DATEDIFF function allows calculating the difference between two dates. The syntax of the function is DATEDIFF(datepart, startdate, enddate). The datepart is shown in Table 2.

DATENAME allows returning the name of the part of the date (see Listing 11).

declare @now datetime;

set @now = getdate();

select datename(dw, @now) as date_name;

Listing 11.

If you want to return part of the date, you can use the DATEPART function whose syntax is DATEPART(datepart,date_to_inspect). The datepart parameter is the same as Table 2.

Let's suppose you want to see the rows associated to the March, 2008 (see Listing 12).

select *

from tbDate

where datepart(yyyy, col)= 2008 and datepart(mm, col)=3;;

Listing 12.

There are also single parameter functions which return the integer values of the day, month, and year (see Listing 13).

select year(getdate()) as [year];

select month(getdate()) as [month];

select day(getdate()) as [day];Listing 13.

Date and time in Oracle database.

Before Oracle 9i the only data type, related to date and time, was DATE. Now we have three other data types with new functionality. Let's describe each of the four Oracle date data type:

• DATE. Stores a date and time, resolved to the second. No include time zone information.
• TIMESTAMP. Stored a date and time without respect to a time zone. It's similar to DATE except of being able to resolve time to the billionth of a second.
• TIMESTAMP WITH TIME ZONE. Stores a date and time along with the time zone.
• TIMESTAMP WITH LOCAL TIME ZONE. Stores a date and time assuming to be in the local time zone.

There are several functions that return current date and time.

Function	Time zone	Datatype returned
CURRENT_DATE	Session	DATE
CURRENT_TIMESTAMP	Session	TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP	Session	TIMESTAMP
SYSDATE	Server	DATE
SYSTIMESTAMP	Server	TIMESTAMP WITH TIME ZONE

Table 3.

If you runt the following Oracle statements (see Listing 14).

begin

  DBMS_OUTPUT.PUT_LINE(CURRENT_TIMESTAMP);

  DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);

end;

Listing 14.

And the output is (see Listing 15).

24-FEB-02 04.55.54.803009000 PM -08:00

24-FEB-02 07.55.54.804221000 PM -05:00Listing 15.

It means that the session is on the US Pacific Standard Time (-8:00) and the server is on the US Eastern Standard Time (-5:00).

Whenever a DATE value is displayed, Oracle will call TO_CHAR automatically with the default DATE format which is ‘DD-MM-YY'. You may override this behavior by calling the TO_CHAR explicitly. The syntax of this function is TO_CHAR(date, 'format'). The format can be formed from the following combinations (see Table 4).

MM	Numeric month (e.g., 07)
MON	Abbreviated month name (e.g., JUL)
MONTH	Full month name (e.g., JULY)
DD	Day of month (e.g., 24)
DY	Abbreviated name of day (e.g., FRI)
YYYY	4-digit year (e.g., 1998)
YY	Last 2 digits of the year (e.g., 98)
RR	Like YY, but the two digits are rounded to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906, for example.
AM	(or PM) Meridian indicator
HH	Hour of day (1-12)
HH24	Hour of day (0-23)
MI	Minute (0-59)
SS	Second (0-59)

Table 4.

Let's see an example (see Listing 16).

select TO_CHAR(dtCol1, 'MONTH, DD YYYY - DY'), TO_CHAR(tsCol2, 'MONTH, DD YYYY - DY'), TO_CHAR(tsCol3, 'MONTH, DD YYYY - DY') from tbDate;

Listing 16.

Let's talk about inputting a date value. This is done using the function TO_DATE and TO_TIMESTAMP which converts a string into a date according to the date format (see Table 4). The syntax of these functions are: TO_DATE(<string>,'format') and TO_TIMESTAMP(<string>,'format'). Let's illustrate with an example (see Listing 17).

The output is (see Listing 18).

24-MAR-08

24-MAR-08 03.00.00.000000 PM

24-MAR-08 03.00.00.000000 PM -05:00

24-MAR-08 03.00.00.000000 PM

Listing 18.

Whenever Oracle expects a date value, it calls automatically this function according to the default date format (NLS_DATE_FORMAT). Alternatively you can call this function and use your own format. You can change the default date format for a given section using the alter section statement (see Listing 19).

alter section set NLS_DATE_FORMAT = 'MM/DD/YYYY';

Listing 19.

The EXTRACT function is used to extract date components from a datetime value. The syntax is EXTRACT(component_name from date_value). The component_name is specified in the following table (see Table 5).

Component name	Return datatype
YEAR	NUMBER
MONTH	NUMBER
DAY	NUMBER
HOUR	NUMBER
MINUTE	NUMBER
SECOND	NUMBER
TIMEZONE_HOUR	NUMBER
TIMEZONE_MINUTE	NUMBER
TIMEZONE_REGION	VARCHAR2
TIMEZONE_ABBR	VARCHAR2

Table 5.

Let's see an example (see Listing 20).

select extract(month from sysdate), extract(year from sysdate)

from dual;

Listing 20.

In Oracle, you can also do some date arithmetic. You can use the ADD_MONTHS to increment and decrement the months in a date. You can also add numeric values to dates, and this value represents days and fractions of day. Let's see the following example. You need to add a day to the current day (see tomorrow) (see Listing 21).

select sysdate+1

from dual;

Listing 21.

You can also add four hours to the current date (see Listing 22).

select TO_CHAR(sysdate+4/24,'MONTH, DD YYYY, HH:MI:SS AM')

from dual;

Listing 22.

Conclusion.

In this article, I covered the main concepts related to date and time values. I illustrated the principles using several examples in the most important database systems today, Microsoft SQL Server and Oracle database.