/***********************************/
/* GRUMPY PODCAST - DATE FUNCTIONS */
/***********************************/

USE [TEST]
GO

/************************/
/* TOPIC 01 - GETDATE() */
/************************/

SELECT GETDATE()
GO

SELECT CONVERT(DATE,GETDATE())
GO

SELECT 'Today''s date is: ' + CONVERT(VARCHAR,CONVERT(DATE,GETDATE()))
GO

/********************/
/* Start of a batch */
/********************/

DECLARE @Today DATE;
DECLARE @DateTime DATETIME;

SET @Today = GETDATE();
SET @DateTime = GETDATE();

PRINT @Today;
PRINT ''
PRINT @DateTime;
GO

/************************/
/* Setting date formats */
/************************/

DECLARE @DateTime DATETIME;

SET @DateTime = GETDATE();

SELECT 
	'Today''s date using format 1 is: ' + CONVERT(VARCHAR,@DateTime,1)		AS DATE_FORMAT_01,
	'Today''s date using format 2 is: ' + CONVERT(VARCHAR,@DateTime,2)		AS DATE_FORMAT_02,
	'Today''s date using format 3 is: ' + CONVERT(VARCHAR,@DateTime,3)		AS DATE_FORMAT_03,
	'Today''s date using format 4 is: ' + CONVERT(VARCHAR,@DateTime,4)		AS DATE_FORMAT_04,
	'Today''s date using format 5 is: ' + CONVERT(VARCHAR,@DateTime,5)		AS DATE_FORMAT_05,
	'Today''s date using format 6 is: ' + CONVERT(VARCHAR,@DateTime,6)		AS DATE_FORMAT_06,
	'Today''s date using format 7 is: ' + CONVERT(VARCHAR,@DateTime,7)		AS DATE_FORMAT_07,

	'Today''s date using format 10 is: ' + CONVERT(VARCHAR,@DateTime,10)	AS DATE_FORMAT_10,
	'Today''s date using format 11 is: ' + CONVERT(VARCHAR,@DateTime,11)	AS DATE_FORMAT_11,
	'Today''s date using format 12 is: ' + CONVERT(VARCHAR,@DateTime,12)	AS DATE_FORMAT_12,

	'Today''s date using format 12 is: ' + CONVERT(VARCHAR,@DateTime,23)	AS DATE_FORMAT_23,

	'Today''s date using format 101 is: ' + CONVERT(VARCHAR,@DateTime,101)	AS DATE_FORMAT_101,
	'Today''s date using format 102 is: ' + CONVERT(VARCHAR,@DateTime,102)	AS DATE_FORMAT_102,
	'Today''s date using format 103 is: ' + CONVERT(VARCHAR,@DateTime,103)	AS DATE_FORMAT_103,
	'Today''s date using format 104 is: ' + CONVERT(VARCHAR,@DateTime,104)	AS DATE_FORMAT_106,
	'Today''s date using format 105 is: ' + CONVERT(VARCHAR,@DateTime,105)	AS DATE_FORMAT_105,
	'Today''s date using format 106 is: ' + CONVERT(VARCHAR,@DateTime,106)	AS DATE_FORMAT_106,
	'Today''s date using format 107 is: ' + CONVERT(VARCHAR,@DateTime,107)	AS DATE_FORMAT_107,

	'Today''s date using format 110 is: ' + CONVERT(VARCHAR,@DateTime,110)	AS DATE_FORMAT_110,
	'Today''s date using format 111 is: ' + CONVERT(VARCHAR,@DateTime,111)	AS DATE_FORMAT_111,
	'Today''s date using format 112 is: ' + CONVERT(VARCHAR,@DateTime,112)	AS DATE_FORMAT_112;
GO


/***********************/
/* TOPIC 02 - ISDATE() */
/***********************/

DECLARE @DateAsText VARCHAR(32)

SET @DateAsText = '2020/04/01'

SELECT ISDATE(@DateAsText)
GO

DECLARE @DateAsText VARCHAR(32)

SET @DateAsText = '2020/04/31'

SELECT ISDATE(@DateAsText)
GO

DECLARE @DateAsText VARCHAR(32)

/***********************/
/* Set an invalid date */
/***********************/

SET @DateAsText = '2020/04/31'

IF ISDATE(@DateAsText) = 1
	PRINT 'Date: ' + @DateAsText +  ' is a valid date string'
ELSE
	PRINT 'Date: ' + @DateAsText +  ' is not a valid date string';
GO

/********************/
/* Set a valid date */
/********************/

DECLARE @DateAsText VARCHAR(32)

SET @DateAsText = '2020/04/30'

IF ISDATE(@DateAsText) = 1
	PRINT 'Date: ' + @DateAsText +  ' is a valid date string'
ELSE
	PRINT 'Date: ' + @DateAsText +  ' is not a valid date string';

GO

/************************/
/* TOPIC 03 - DATEADD() */
/************************/

/*******/
/* Day */
/*******/

DECLARE @Today DATE;

SET @Today = GETDATE()

PRINT 'Today''s date is: ' + CONVERT(VARCHAR,@Today)

PRINT 'Today''s date plus 1 day is: ' + CONVERT(VARCHAR,DATEADD(dd,1,@Today))

PRINT 'Today''s date minus 1 day is: ' + CONVERT(VARCHAR,DATEADD(dd,-1,@Today))
GO

/*********/
/* Month */
/*********/

DECLARE @Today DATE;

SET @Today = GETDATE()

PRINT 'Today''s date is: ' + CONVERT(VARCHAR,@Today)

PRINT 'Today''s date plus 1 month is: ' + CONVERT(VARCHAR,DATEADD(mm,1,@Today))

PRINT 'Today''s date minus 1 month is: ' + CONVERT(VARCHAR,DATEADD(mm,-1,@Today))
GO

/***********/
/* Quarter */
/***********/

DECLARE @Today DATE;

SET @Today = GETDATE()

PRINT 'Today''s date is: ' + CONVERT(VARCHAR,@Today)

PRINT 'Today''s date plus 1 quarter is: ' + CONVERT(VARCHAR,DATEADD(qq,1,@Today))

PRINT 'Today''s date minus 1 quarter is: ' + CONVERT(VARCHAR,DATEADD(qq,-1,@Today))

PRINT 'Today''s date minus 12 quarters is: ' + CONVERT(VARCHAR,DATEADD(qq,-12,@Today))
GO

/********/
/* Year */
/********/

DECLARE @Today DATE;

SET @Today = GETDATE()

PRINT 'Today''s date is: ' + CONVERT(VARCHAR,@Today)

PRINT 'Today''s date plus 1 year is: ' + CONVERT(VARCHAR,DATEADD(yy,1,@Today))

PRINT 'Today''s date minus 1 year is: ' + CONVERT(VARCHAR,DATEADD(yy,-1,@Today))
GO


/*************************/
/* TOPIC 04 - DATEDIFF() */
/*************************/

DECLARE @Today DATE;
DECLARE @TodayLastYear DATE;

SET @Today = GETDATE();
SET @TodayLastYear = DATEADD(yy,-1,@Today);

SELECT 'Difference in years: ' + CONVERT(VARCHAR,DATEDIFF(yy,@Today,@TodayLastYear))
UNION ALL
SELECT 'Difference in quarters: ' + CONVERT(VARCHAR,DATEDIFF(qq,@Today,@TodayLastYear))
UNION ALL
SELECT 'Difference in months: ' + CONVERT(VARCHAR,DATEDIFF(mm,@Today,@TodayLastYear))
UNION ALL
SELECT 'Difference in weeks: ' + CONVERT(VARCHAR,DATEDIFF(ww,@Today,@TodayLastYear))
UNION ALL
SELECT 'Difference in days: ' + CONVERT(VARCHAR,DATEDIFF(dd,@Today,@TodayLastYear))
GO
 
/*************************/
/* TOPIC 05 - DATEPART() */
/*************************/

DECLARE @Today DATE;

SET @Today = GETDATE();

SELECT 'Today''s date is: ' + CONVERT(VARCHAR,@Today) AS [Part of the Date]
UNION ALL
SELECT 'The Year part is: ' + CONVERT(VARCHAR,DATEPART(yy,@Today))
UNION ALL
SELECT 'The Quarter part is: ' + CONVERT(VARCHAR,DATEPART(qq,@Today))
UNION ALL
SELECT 'The Month part is: ' + CONVERT(VARCHAR,DATEPART(mm,@Today))
UNION ALL
SELECT 'The week part is: ' + CONVERT(VARCHAR,DATEPART(ww,@Today))
UNION ALL
SELECT 'The day part is: ' + CONVERT(VARCHAR,DATEPART(dd,@Today))
GO

/**********************/
/* TOPIC 06 - EOMONTH */
/**********************/

SELECT GETDATE() AS Today,EOMONTH(GETDATE()) AS [EOM Date]
GO

SELECT GETDATE() AS Today,EOMONTH(
	GETDATE(),
	1 -- month to add
	) AS [EOM Date]
GO

SELECT GETDATE() AS Today,EOMONTH(
	GETDATE(),
	7 -- month to add
	) AS [EOM Date]
GO

SELECT GETDATE() AS Today,EOMONTH(
	GETDATE(),
	-1 -- month to add
	) AS [EOM Date]
GO

SELECT GETDATE() AS Today,EOMONTH(
	GETDATE(),
	-7 -- month to add
	) AS [EOM Date]
GO

SELECT GETDATE() AS Today,EOMONTH(
	GETDATE(),
	12 -- month to add
	) AS [EOM Date]
GO