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.