… Intelligence is a sum of the parts of the whole

An excellent post on this subject here (full text displayed under the link):

http://sql-troubles.blogspot.com/2010/02/oracle-vs-sql-server-date-conversion.html

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

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

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.