SQL Query: Calculate FiscalMonth, FiscalYear from Date

DECLARE @today varchar(12);
DECLARE @today1 datetime2;

SET @today=’2016/01/31′;
SELECT @today;
SELECT DATEPART(MM, @today) as month;
SELECT DATEPART(MM, DATEADD(MM,-3, @today)) as FiscalMonthNumber;
SELECT DATEPART(YY, DATEADD(MM,-3, @today)) as FiscalYear1;
SELECT RIGHT(CONVERT(INT, DATEPART(YY, DATEADD(MM,-3, @today)) +1), 2) as FiscalYear2;
SELECT CONVERT(VARCHAR, DATEPART(YY, DATEADD(MM,-3, @today)))
+ ‘/’
+ CONVERT(VARCHAR, RIGHT(CONVERT(INT, DATEPART(YY, DATEADD(MM,-3, @today)) +1), 2)) AS FiscalYear;
–SELECT CAST(CONVERT(VARCHAR(8),@today,112) as int); — all versions

SET @today1=convert(varchar(12),@today);
SELECT @today1 as “today1”
SELECT CONVERT(int, (CONVERT(varchar(8),@today1,112))) as intdate; –convert date to int
SELECT CONVERT(varchar(8),@today,112) as chardate; –convert date to varchar

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s