DB Karşılaştırmalı Tarih Fonksiyonları(Oracle-Postgresql-Mssql-Mysql)

Sevgili arkadaşım Onur Erdal ile beraber hazırladığımız bu güzel  çalışma için kendisine teşekkür ederim.

DESCRIPTION	ORACLE	POSTGRESQL	SQL SERVER	MYSQL
DATE TIPS
FIRST DAY OF CURRENT MONTH	TRUNC(SYSDATE,'MM')	cast(date_trunc('month',current_date) as date)	DATEADD(month, DATEDIFF(month, 0, getdate()), 0)	DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), interval 30 day)),INTERVAL 1 DAY)
FIRST DAY OF LAST MONTH	ADD_MONTHS(TRUNC(SYSDATE,'MM'),-1)	cast(date_trunc('month',current_date-interval '1 month') as date)	DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)	DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), interval 60 day)),INTERVAL 1 DAY)
FIRST DAY OF NEXT MONTH	ADD_MONTHS(TRUNC(SYSDATE,'MM'),+1)	cast(date_trunc('month',current_date+interval '1 month') as date)	DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+1, 0)	DATE_ADD(LAST_DAY(CURRENT_DATE()),INTERVAL 1 DAY)
LAST DAY OF CURRENT MONTH	TRUNC(LAST_DAY(SYSDATE))	cast(date_trunc('month',current_date+interval '1 month') as date)-1	DATEADD(month, DATEDIFF(month, 0 , GETDATE ())+1, -1)	LAST_DAY(CURRENT_DATE())
LAST DAY OF LAST MONTH	LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'MM'),-1))	cast(date_trunc('month',current_date) as date)-1	DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)	LAST_DAY(DATE_SUB(CURRENT_DATE(),INTERVAL 1 MONTH))
LAST DAY OF NEXT MONTH	LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'MM'),+1))	cast(date_trunc('month',current_date+interval '2 month') as date)-1	DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+2, -1)	LAST_DAY(DATE_ADD(CURRENT_DATE(),INTERVAL 1 MONTH))
CURRENT DATE	TRUNC(SYSDATE)	current_date	GETDATE()	CURRENT_DATE()
YESTERDAY	TRUNC(SYSDATE-1)	current_date-1	GETDATE()-1	DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY)
TOMORROW	TRUNC(SYSDATE+1)	current_date+1	GETDATE()+1	DATE_ADD(CURRENT_DATE(),INTERVAL 1 DAY)
THIS YEAR	EXTRACT(YEAR FROM SYSDATE)	extract (year from current_date)	YEAR(GETDATE())	EXTRACT(YEAR FROM CURRENT_DATE())
NEXT YEAR	EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,12))	extract(year from current_date+interval '1 year')	YEAR(GETDATE())+1	EXTRACT(YEAR FROM DATE_ADD(CURRENT_DATE(),INTERVAL 1 YEAR))
LAST YEAR	EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE,-12))	extract(year from current_date-interval '1 year')	YEAR(GETDATE())-1	EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE(),INTERVAL 1 YEAR))
LAST MONTH	EXTRACT(MONTH FROM ADD_MONTHS(SYSDATE,-1))	extract(month from current_date-interval '1 month')	MONTH(GETDATE())-1	EXTRACT(MONTH FROM DATE_SUB(CURRENT_DATE(),INTERVAL 1 MONTH))
NEXT MONTH	EXTRACT(MONTH FROM ADD_MONTHS(SYSDATE,+1))	extract(month from current_date+interval '1 month')	MONTH(GETDATE())+1	EXTRACT(MONTH FROM DATE_ADD(CURRENT_DATE(),INTERVAL 1 MONTH))
THIS MONTH	EXTRACT(MONTH FROM SYSDATE)	extract(month from current_date)	MONTH(GETDATE())	EXTRACT(MONTH FROM CURRENT_DATE())
LAST DAY OF THIS YEAR	ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12)-1	cast(date_trunc('year',current_date) as date)+interval '1 year' -interval '1 day'	DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1)	LAST_DAY(DATE_ADD(CURRENT_DATE() , INTERVAL 12-MONTH(NOW()) MONTH))
LAST DAY OF LAST YEAR	LAST_DAY(ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), -1))	cast(date_trunc('year',current_date) as date)-interval '1 day'	DATEADD(yy, DATEDIFF(yy, 0, GETDATE())  , -1)	DATE_SUB(LAST_DAY(DATE_ADD(CURRENT_DATE() , INTERVAL 12-MONTH(NOW()) MONTH)),INTERVAL 1 YEAR)
LAST DAY OF NEXT YEAR	ADD_MONTHS(TRUNC (SYSDATE, 'YEAR'),24)-1	cast(date_trunc('year',current_date) as date)+interval '2 year' -interval '1 day'	DATEADD(yy, DATEDIFF(yy, 0, GETDATE())  +2, -1)	DATE_ADD(LAST_DAY(DATE_ADD(CURRENT_DATE() , INTERVAL 12-MONTH(NOW()) MONTH)),INTERVAL 1 YEAR)
DAY NUMBER OF MONTH	TO_NUMBER(TO_CHAR(SYSDATE,'DD'))	date_part('day',current_date)	DAY(GETDATE())	DAY(CURRENT_DATE())
DAY NUMBER OF WEEK	TO_CHAR(SYSDATE,'D') /*TO START FROM MONDAY ADD -1 END OF STATEMENT*/	to_char(current_date,'D')::INTEGER /*TO START FROM MONDAY ADD -1 END OF STATEMENT*/	DATEPART(dw,GETDATE())/*TO START FROM MONDAY ADD -1 END OF STATEMENT*/	DAYOFWEEK(CURRENT_DATE())/*TO START FROM MONDAY ADD -1 END OF STATEMENT*/
DAY NUMBER OF YEAR	TO_NUMBER(TO_CHAR(SYSDATE,'DDD'))	to_char(current_date,'DDD')::INTEGER	DATEPART(dy,GETDATE())	DAYOFYEAR(CURRENT_DATE())
IS WEEKEND	CASE WHEN TO_NUMBER(TO_CHAR(YOUR_DATE,'D')) IN (7,1) THEN 1 ELSE 0 END/*TO START FROM MONDAY ADD -1 END OF STATEMENT AND CHANGE ‘IN’ CONDITION WITH (6,7)*/	case when to_char(YOUR_DATE,'D')::INTEGER in (1,7) then 1 else 0 end/*TO START FROM MONDAY ADD -1 END OF STATEMENT AND CHANGE ‘IN’ CONDITION WITH (6,7)*/	CASE WHEN DATEPART(dw,GETDATE()) IN (7,1) THEN 1 ELSE 0 END/*TO START FROM MONDAY ADD -1 END OF STATEMENT AND CHANGE ‘IN’ CONDITION WITH (6,7)*/	CASE WHEN DAYOFWEEK(CURRENT_DATE()) IN (7,1) THEN 1 ELSE 0 END /*TO START FROM MONDAY ADD -1 END OF STATEMENT AND CHANGE ‘IN’ CONDITION WITH (6,7)*/
GET DAY NAME	TO_CHAR(SYSDATE, 'DAY','NLS_DATE_LANGUAGE=TURKISH')	TO_CHAR(current_date,'DAY')/*For the target language you can write case statement.There is no nls format*/	DATENAME(DW, GETDATE() )/*For the target language you can SET LANGUAGE Turkish */	DAYNAME(CURRENT_DATE()) GETDATE() )/*For the target language you can SET @@lc_time_names = 'tr_TR' */
GET MONTH NAME	TO_CHAR(SYSDATE, 'MONTH','NLS_DATE_LANGUAGE=TURKISH')	TO_CHAR(current_date,'MONTH') )/*For the target language you can write case statement.There is no nls format*/	DATENAME(MM, GETDATE()))/*For the target language you can SET LANGUAGE Turkish	MONTHNAME(CURRENT_DATE()) /*For the target language you can SET @@lc_time_names = 'tr_TR' */
GET DAY COUNT BETWEEN TWO DATES	END_DATE-START_DATE	date_part('DAY',END_DATE -START_DATE)	DATEDIFF ( day , START_DATE, END_DATE)	END_DATE-START_DATE
GET WEEK COUNT BETWEEN TWO DATES	(NEXT_DAY(END_DATE,'MONDAY')-NEXT_DAY(START_DATE,'MONDAY'))/7	(cast(date_trunc('week',END_DATE) as DATE)-cast(date_trunc('week',START_DATE) as DATE))/7	DATEDIFF (week , START_DATE, END_DATE)	DATEDIFF(CURRENT_DATE() ,(CURRENT_DATE()-100))/7 /*For rounded number you can use floor(),ceil() or round() */
GET MONTH COUNT BETWEEN TWO DATES	MONTHS_BETWEEN(END_DATE,START_DATE)	DATE_PART('YEAR',AGE(END_DATE, START_DATE))*12+DATE_PART('MONTH',AGE(END_DATE, START_DATE))	DATEDIFF (month , START_DATE, END_DATE)	TIMESTAMPDIFF(MONTH, START_DATE, END_DATE)
CAST STRING TO DATE	TO_DATE('2022-07-06','YYYY-MM-DD')	TO_DATE('2022-07-07','YYYY-MM-DD')	CONVERT(DATE,'13/12/2019')	STR_TO_DATE('07,7,2022','%d,%m,%Y')
CAST DATE TO STRING	TO_CHAR(SYSDATE,'YYYY-MM-DD')	TO_CHAR(current_date,'YYYY-MM-DD')	CONVERT(VARCHAR, GETDATE())	CAST(CURRENT_DATE() AS NCHAR)
CAST STRING TO DATETIME	TO_DATE('2022-07-06 23:58:12','YYYY-MM-DD HH24:MI:SS')	TO_DATE('2022-07-07 10:47:52','YYYY-MM-DD HH24:MI:SS')	CONVERT(DATETIME, '2022-07-07 10:47:52')	CAST('2022-07-07 10:47:52' AS DATETIME)
GET HOUR COUNT BETWEEN TWO DATES	24 * (END_DATE – START_DATE)	EXTRACT(EPOCH FROM END_DATE-START_DATE)/3600	DATEDIFF (HOUR , START_DATE, END_DATE)	TIMESTAMPDIFF(HOUR, START_DATE, END_DATE)
GET MINUTE COUNT BETWEEN TWO DATES	60*24 * (END_DATE – START_DATE)	EXTRACT(EPOCH FROM END_DATE-START_DATE)/60	DATEDIFF (MINUTE , START_DATE, END_DATE)	TIMESTAMPDIFF(MINUTE , START_DATE, END_DATE)
GET SECOND COUNT BETWEEN TWO DATES	60*60*24 * (END_DATE – START_DATE)	EXTRACT(EPOCH FROM END_DATE-START_DATE)	DATEDIFF (SECOND , START_DATE, END_DATE)	TIMESTAMPDIFF(SECOND , START_DATE, END_DATE)
ADD MONTHS TO A DATE	ADD_MONTHS(YOUR_DATE,1)	YOUR_DATE+interval '1 MONTH'	DATEADD(MONTH, 1 , YOUR_DATE)	DATE_ADD(YOUR_DATE , INTERVAL 1 MONTH)
ADD DAYS TO A DATE	YOUR_DATE+5	YOUR_DATE+interval '7 DAY'	DATEADD(DAY, 1 , YOUR_DATE)	DATE_ADD(YOUR_DATE , INTERVAL 1 DAY)
CAST DATETIME TO DATE	TRUNC(YOUR_DATE)	CAST(YOUR_DATE as DATE)	CONVERT(DATE, YOUR_DATE)	CAST('2022-07-07 10:47:52' AS DATE)
FIRST DAY OF THIS YEAR	TRUNC(SYSDATE,'YEAR')	cast(DATE_TRUNC('YEAR',current_date) as DATE)	DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)	MAKEDATE(YEAR(CURRENT_DATE()),1)
FIRST DAY OF LAST YEAR	TRUNC(TRUNC(SYSDATE,'YEAR')-1,'YEAR')	cast(DATE_TRUNC('YEAR',current_date-interval '1 YEAR') as DATE)	DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0)	MAKEDATE(YEAR(CURRENT_DATE())-1,1)
FIRST DAY OF NEXT YEAR	ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),12)	cast(DATE_TRUNC('YEAR',current_date+interval '1 YEAR') as DATE)	DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) +1, 0)	MAKEDATE(YEAR(CURRENT_DATE())+1,1)
GET NEXT MONDAY	NEXT_DAY(SYSDATE,'MONDAY')	cast(date_trunc('week',current_date+interval '7 DAY') as DATE)	DATEADD(DAY, 1, GETDATE() - DATEPART(dw, GETDATE())+ CASE WHEN DATEPART(dw, GETDATE()) < 1 THEN 0 ELSE 7 END ) /* 1…7 CORRESPONDS TO MONDAY …. SUNDAY*/	DATE(ADDDATE(NOW(), 2 - DAYOFWEEK(NOW()) + CASE WHEN DAYOFWEEK(NOW()) < 2 THEN 0 ELSE 7 END )) /* 1…7 CORRESPONDS TO SUNDAY …. SATURDAY*/
GET DIFFERENT MONTHS COUNT BETWEEN TWO DATES	CASE WHEN EXTRACT( YEAR FROM END_DATE )=EXTRACT (YEAR FROM START_DATE) THEN EXTRACT( MONTH FROM END_DATE )-EXTRACT (MONTH FROM START_DATE)+1 WHEN EXTRACT( YEAR FROM END_DATE )-EXTRACT (YEAR FROM START_DATE)=1 THEN EXTRACT( MONTH FROM END_DATE )+12-EXTRACT (MONTH FROM START_DATE)+1 WHEN EXTRACT( YEAR FROM END_DATE )-EXTRACT (YEAR FROM START_DATE)>1 THEN (EXTRACT( YEAR FROM END_DATE )-EXTRACT (YEAR FROM START_DATE)-1)*12+ EXTRACT( MONTH FROM END_DATE )+12-EXTRACT (MONTH FROM START_DATE)+1 END	extract(year from AGE(END_DATE,START_DATE))*12+ extract(MONTH from AGE(END_DATE,START_DATE))+1	CASE WHEN YEAR(END_DATE)= YEAR(START_DATE) THEN MONTH(END_DATE)-MONTH(START_DATE)+1 WHEN YEAR(END_DATE)- YEAR(START_DATE)=1 THEN MONTH(END_DATE)+12-MONTH(START_DATE)+1 WHEN YEAR(END_DATE)- YEAR(START_DATE)>1 THEN (YEAR(END_DATE)- YEAR(START_DATE)-1)*12+ MONTH(END_DATE)+12-MONTH(START_DATE)+1 END	CASE WHEN EXTRACT(YEAR FROM END_DATE)= EXTRACT(YEAR FROM START_DATE) THEN EXTRACT(MONTH FROM END_DATE)-EXTRACT(MONTH FROM END_DATE)+1 WHEN EXTRACT(YEAR FROM END_DATE)- EXTRACT(YEAR FROM START_DATE)=1 THEN EXTRACT(MONTH FROM END_DATE)+12-EXTRACT(MONTH FROM END_DATE)+1 WHEN EXTRACT(YEAR FROM END_DATE)- EXTRACT(YEAR FROM START_DATE)>1 THEN (EXTRACT(YEAR FROM END_DATE)- EXTRACT(YEAR FROM START_DATE)-1)*12+ EXTRACT(MONTH FROM END_DATE)+12-EXTRACT(MONTH FROM END_DATE)+1 END
GET DIFFERENT DAYS COUNT BETWEEN TWO DATES	TRUNC(END_DATE)-TRUNC(START_DATE)+1	extract(year from AGE(END_DATE,START_DATE))*365+ extract(MONTH from AGE(END_DATE,START_DATE))*30+ extract(DAY from AGE(END_DATE,START_DATE))+1	DATEDIFF (DAY , START_DATE, END_DATE) + 1	TIMESTAMPDIFF(HOUR, START_DATE, END_DATE)+1
GET DIFFERENT HOURS COUNT BETWEEN TWO DATES	CASE WHEN TRUNC(END_DATE)-TRUNC(START_DATE)=0 THEN TO_NUMBER(TO_CHAR(END_DATE,'HH24'))-TO_NUMBER(TO_CHAR(START_DATE,'HH24'))+1 WHEN TRUNC(END_DATE)-TRUNC(START_DATE)=1 THEN  TO_NUMBER(TO_CHAR(END_DATE,'HH24'))+24-TO_NUMBER(TO_CHAR(START_DATE,'HH24'))+1 WHEN TRUNC(END_DATE)-TRUNC(START_DATE)>1 THEN (TRUNC(END_DATE)-TRUNC(START_DATE)-1)*24+ TO_NUMBER(TO_CHAR(END_DATE,'HH24'))+24-TO_NUMBER(TO_CHAR(START_DATE,'HH24'))+1 END	extract(day from END_DATE-START_DATE))*24+ extract(HOUR from END_DATE-START_DATE)+1	CASE WHEN DATEDIFF (DAY , CONVERT(DATE , START_DATE), CONVERT(DATE , END_DATE))=0 THEN DATEPART(HOUR, END_DATE)-DATEPART(HOUR, START_DATE)+1 WHEN DATEDIFF (DAY , CONVERT(DATE , START_DATE), CONVERT(DATE , END_DATE))=1 THEN DATEPART(HOUR, END_DATE)+24-DATEPART(HOUR, START_DATE)+1  WHEN DATEDIFF (DAY , CONVERT(DATE , START_DATE), CONVERT(DATE , END_DATE))>1 THEN DATEDIFF (DAY , CONVERT(DATE , START_DATE), CONVERT(DATE , END_DATE-1))*24+ DATEPART(HOUR, END_DATE)+24-DATEPART(HOUR, START_DATE)+1 END	CASE WHEN TIMESTAMPDIFF(DAY, START_DATE, END_DATE)=0 THEN EXTRACT(HOUR FROM END_DATE)-EXTRACT(HOUR FROM START_DATE)+1 WHEN TIMESTAMPDIFF(DAY, START_DATE, END_DATE)=1 THEN  EXTRACT(HOUR FROM END_DATE)+24-EXTRACT(HOUR FROM START_DATE)+1 WHEN TIMESTAMPDIFF(DAY, START_DATE, END_DATE)>1 THEN (TIMESTAMPDIFF(DAY, START_DATE, END_DATE)-1)*24+ EXTRACT(HOUR FROM END_DATE)+24-EXTRACT(HOUR FROM START_DATE)+1 END
GET MAX DATE OF MANY COLUMNS FOR A ROW	GREATEST(DATE1,DATE2,…)	GREATEST(DATE1,DATE2,…)	/*On Azure*/ GREATEST(DATE1,DATE2,…) /*On Prem*/ SELECT Your group by columns,MAX(x.CombinedDate) AS greatest FROM   YourTable AS u CROSS APPLY ( VALUES ( u.Date1 ), ( u.Date2 )) AS x ( CombinedDate ) group by Your group by columns	GREATEST(DATE1,DATE2,…)