About Me

IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.

Sunday, February 14, 2010

Oracle vs. SQL Server: Date Conversion

    During data conversions, data migrations or also during simple processing of data is requested to format dates to a given format, extract a given time unit or convert a string to a date data type. Even if Oracle and SQL Server provides several functions for this purpose, there are small techniques that could help make things easier.

    In SQL Server the DatePart and DateName functions can be used to extract the various type of time units, the first function returning always an integer, while the second returns a character string, allowing thus to get the name of the current month or of the current day of the week, otherwise the output being quite similar.
-- SQL Server DatePart
SELECT GETDATE() CurrentDate
,
DatePart(ss, GETDATE()) SecondPart

,
DatePart(mi, GETDATE()) MinutePart

,
DatePart(hh, GETDATE()) MinutePart

,
DatePart(d, GETDATE()) DayPart

,
DatePart(wk, GETDATE()) WeekPart

,
DatePart(mm, GETDATE()) MonthPart

,
DatePart(q, GETDATE()) QuaterPart

,
DatePart(yyyy, GETDATE()) YearPart
 
-- SQL Server DateName
SELECT GETDATE() CurrentDate
,
DateName(ss, GETDATE()) SecondPart

,
DateName(mi, GETDATE()) MinutePart

,
DateName(hh, GETDATE()) MinutePart

,
DateName(d, GETDATE()) DayPart

,
DateName(wk, GETDATE()) WeekPart

,
DateName(mm, GETDATE()) MonthPart

,
DateName(q, GETDATE()) QuaterPart

,
DateName(yyyy, GETDATE()) YearPart
, DateName(mm, GETDATE()) MonthName
,
DateName(dd, GETDATE()) DayName


    SQL Server provides three quite useful functions for getting the Day, Month or Year of a given date:
-- SQL Server alternative functions
SELECT DAY(GetDate()) DayPart
,
MONTH(GetDate()) MonthPart
, YEAR(GetDate()) YearPar

    Oracle provides a more flexible alternative of DateName function, respectively the TO_CHAR function, that allow not only the extraction of the different time units, but also the conversion of a date to a specified format.
-- Oracle Date parts
SELECT to_char(SYSDATE, 'dd-mon-yyyy hh24:mi:ss') CurrentDate
,
to_char(SYSDATE, 'SS') SecondPart

,
to_char(SYSDATE, 'MI') MinutePart

,
to_char(SYSDATE, 'HH') HourPart

,
to_char(SYSDATE, 'DD') DayPart

,
to_char(SYSDATE, 'IW') WeekPart

,
to_char(SYSDATE, 'MM') MonthPart

,
to_char(SYSDATE, 'QQ') QuarterPart

,
to_char(SYSDATE, 'YYYY') YearPart

,
to_char(SYSDATE, 'MONTH') MonthName

,
to_char(SYSDATE, 'DAY') DayName
FROM
DUAL

-- Oracle Date formatting
SELECT to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') CurrentDate
,
to_char(SYSDATE, 'Mon dd yyyy hh24:mi') USDateFormat

,
to_char(SYSDATE, 'mm/dd/yyyy') ANSIDateFormat

,
to_char(SYSDATE, 'yyyy.mm.dd') BritishDateFormat

,
to_char(SYSDATE, 'dd/mm/yyyy') GermanDateFormat

,
to_char(SYSDATE, 'dd-mm-yyyy') ItalianDateFormat

,
to_char(SYSDATE, 'yyyy/mm/dd') JapanDateFormat

,
to_char(SYSDATE, 'yyyymmdd') ISODateFormat

,
to_char(SYSDATE, 'dd Mon yyyy hh24:mi:ss') EuropeDateFormat

,
to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') ODBCDateFormat

,
Replace(to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'), ' ', 'T') ISO8601DateFormat

FROM
DUAL

Date Oracle fomatting
    Even if there are more plausible combinations, the above examples could be used as a starting point, they being chosen to match the similar functionality provided by SQL Server using the CONVERT function and styles.
-- SQL Server date formatting
SELECT GETDATE() CurrentDate
,
CONVERT(varchar(20), GETDATE(), 100) USDateFormat

,
CONVERT(varchar(20), GETDATE(), 101) ANSIDateFormat

,
CONVERT(varchar(20), GETDATE(), 102) BritishDateFormat

,
CONVERT(varchar(20), GETDATE(), 103) GermanDateFormat

,
CONVERT(varchar(20), GETDATE(), 105) ItalianDateFormat

,
CONVERT(varchar(20), GETDATE(), 111) JapanDateFormat

,
CONVERT(varchar(20), GETDATE(), 112) ISODateFormat

,
CONVERT(varchar(20), GETDATE(), 113) EuropeDateFormat

,
CONVERT(varchar(20), GETDATE(), 120) ODBCDateFormat

,
CONVERT(varchar(20), GETDATE(), 126) ISO8601DateFormat

Date SQL Server formatting
    The use of CONVERT function with styles is not the best approach though it saves the day. When the same formatting is used in multiple objects it makes sense to encapsulate the used date conversions in a function, making thus easier their use and their maintenance in case of changes of formatting.
CREATE FUNCTION dbo.GetDateAsString( @date datetime)
RETURNS varchar(10)

AS
BEGIN
    RETURN CONVERT(varchar(10), @date, 103)
END

    The inverse problem is the conversion of a string to a date, Oracle providing the TO_DATE, CAST, TO_TIMESTAMP and  TO_TIMESTAMP_TZ functions for this purpose, the first two functions being the most used.
--Oracle String to Date Conversion
SELECT TO_DATE('25-03-2009', 'DD-MM-YYYY')
,
TO_DATE('25-03-2009 18:30:23', 'DD-MM-YYYY HH24:MI:SS')
,
Cast('25-MAR-2009' as Date)

FROM
DUAL
    Excepting the CONVERT function mentioned above, SQL Server provides a CAST function too, both allowing the conversion of strings to date.
SELECT CAST('2009-03-25' as date)
,
CONVERT(date, '2009-03-25')


  When saving dates into text attributes in SQL Server it should be targeted to use the ISO format which is independent of the format set by DATEFORMAT, otherwise, in case the format of the date stored is known, the string could be translated to the ISO format like in the below function:
--SQL Server: DD/MM/YYYY  String to Date function
CREATE
FUNCTION dbo.GetStringDate(

@date varchar(10))
RETURNS datetime
AS
BEGIN
     RETURN Cast(Right(@date, 4) + '/' + Substring(@date, 4,2) + '/' + Left(@date, 2) as datetime)
END
SELECT
dbo.GetStringDate('25/09/2009')


    Other approach I found quite useful in several cases is based on the VBScript DateSerial function that allows the creation of a date from its constituents:
-- SQL Server: DateSerial
CREATE FUNCTION dbo.DateSerial(
@year int
, @month smallint ,
@day smallint)

RETURNS
date
AS
BEGIN
RETURN (Cast(@year as varchar(4)) + '-' + Cast(@month as varchar(2)) + '-' + Cast(@day as varchar(2)))
END
SELECT
dbo.DateSerial(2009,10,24)

-- SQL Server: DateTimeSerial
CREATE FUNCTION dbo.DateTimeSerial(
@year int
, @month smallint
,
@day smallint

,
@hour smallint

,
@minute smallint

,
@second smallint)

RETURNS
datetime AS
BEGIN
RETURN (Cast(@year as varchar(4)) + '-' + Cast(@month as varchar(2)) + '-' + Cast(@day as varchar(2))
+ ' ' + Cast(@hour as varchar(2)) + ':' + Cast(@minute as varchar(2)) + ':' + Cast(@second as varchar(2)))
END
    Given TO_DATE function’s flexibility none of the three above functions - GetStringDate, DateSerial and DateTimeSerial, are really needed in Oracle.

No comments: