15 February 2010

SQL Reloaded: 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 7


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:

Related Posts Plugin for WordPress, Blogger...

About Me

My photo
IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.