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.