Datetime Formats and Manipulations in SQL

When it comes to reporting there is always a need to modify the date columns to show in different formats, so let’s start seeing how things are done

 

To change the format of the date column to a different format we use FORMAT function in SQL Server 2012 and TO_CHAR function in Oracle as shown below.

 

SQL Server FORMAT (value, format [, culture ] )

 

Example:

Select format(getdate(),'MMddyyyy')

 

as todays date is 06/20/2012 the output of the query is "06202012", so based on what we specify as a second parameter to function we will get different results, below are the list of parameters which can be used to get desired format.

 

Note: As of now format is case sensitive so make sure to use "MM" for month not "mm"

 

“format” parameters:

year

yy, yyyy

month

MM, M

month name

MMM, MMMMM

day

dd, d

hour

hh, for 24 hours its HH

minute

m

second

ss, s

millisecond

fffff 

 

Oracle TO_CHAR( datevalue, [ format_mask ] )

Example:

select

to_char(sysdate,'MM/DD/YY')

from dual;

 

as todays date is 06/20/2012 the output of the query is "06202012", so based on what we specify as a second parameter ([format_mask]) to function we will get different results, below are the list of parameters which can be used to get desired format.

 

“format_mask” parameters:

year

YY, YYYY

month

MM

month name

MON, MONTH

day

DD, D

hour

hh

minute

mi

second

ss

milli second

sssss

 

Other Manipulations

 

Let’s see some other useful manipulations below; the last day of a month can be obtained using below script.

 

SQL Server

Select DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,GETDATE())+1,0))

 

If we change the value highlighted in red to 0 we get the last month...so on

 

SQL Server 2012

Select EOMONTH ( getdate(),1 )

 

Oracle

SELECT SYSDATE,

       LAST_DAY(SYSDATE)

from   DUAL;

 

The date of occurrence of a specific day of a week can be obtained using below script.

 

SQL Server

Select DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0

 

If we change the value highlighted in red to 1 then we get Tuesday...so on.

 

Oracle

SELECT SYSDATE,

       NEXT_DAY(SYSDATE, 'MONDAY')

from   DUAL;

 

Thank you for reading this article, check out my other SQL Server vs. Oracle posts.