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
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.
No comments:
Post a Comment