Get Date Entity

I’ve created an scalar function who returns the following date parts: year, month, day, quarter, week, hour, minute, second, day of year, day of week, date format (yyymmdd, yymmdd, ddmmyyyy, ddmmyy, ddmm, mmdd).

CREATE FUNCTION [dbo].[GetDateEntity]
(@entity_type INT)
RETURNS INT
AS
  BEGIN

    DECLARE @returnValue INT;
	SET @returnValue = -1;

	SELECT @returnValue = CASE @entity_type
	   WHEN 0 THEN (SELECT datename(year,getdate()) AS year)
	   WHEN 1 THEN (SELECT month(getdate()) as month)
	   WHEN 2 THEN (SELECT day(getdate()) as day)
	   WHEN 3 THEN (SELECT datename(quarter,getdate()) as quarter)
	   WHEN 4 THEN (SELECT datename(week,getdate()) as week)
	   WHEN 5 THEN (SELECT datename(hour,getdate())as hour)
	   WHEN 6 THEN (SELECT datename(minute,getdate()) as minute)
	   WHEN 7 THEN (SELECT datename(second,getdate()) as second)
	   WHEN 8 THEN (SELECT datename(dy,getdate()) as day_of_year)
	   --SET DATEFIRST 1; - in function you can not use set; that is why the value is -1 :)
	   WHEN 9 THEN (SELECT datepart(dw,getdate())-1 as day_of_week)
	   WHEN 10 THEN (SELECT convert(varchar, getdate(), 112) as yyymmdd)
	   WHEN 11 THEN (SELECT [dbo].[FormatDate] (CURRENT_TIMESTAMP,'YYMMDD') as yymmdd)
	   WHEN 12 THEN (SELECT [dbo].[FormatDate] (CURRENT_TIMESTAMP,'DDMMYYYY') as ddmmyyyy)
	   WHEN 13 THEN (SELECT [dbo].[FormatDate] (CURRENT_TIMESTAMP,'DDMMYY') as ddmmyy)
	   WHEN 14 THEN (SELECT [dbo].[FormatDate] (CURRENT_TIMESTAMP,'DDMM') as ddmm)
	   WHEN 15 THEN (SELECT [dbo].[FormatDate] (CURRENT_TIMESTAMP,'MMDD') as mmdd)
	END

    RETURN @returnValue;
  END

CREATE FUNCTION [dbo].[GetDateEntity]

(@entity_type INT)

RETURNS INT

BEGIN

DECLARE @returnValue INT;

SET @returnValue = -1;

SELECT @returnValue = CASE @entity_type

WHEN 0 THEN (SELECT datename(year,getdate()) AS year)

WHEN 1 THEN (SELECT month(getdate()) as month)

WHEN 2 THEN (SELECT day(getdate()) as day)

WHEN 3 THEN (SELECT datename(quarter,getdate()) as quarter)

WHEN 4 THEN (SELECT datename(week,getdate()) as week)

WHEN 5 THEN (SELECT datename(hour,getdate())as hour)

WHEN 6 THEN (SELECT datename(minute,getdate()) as minute)

WHEN 7 THEN (SELECT datename(second,getdate()) as second)

WHEN 8 THEN (SELECT datename(dy,getdate()) as day_of_year)

--SET DATEFIRST 1; - in function you can not use set; that is why the value is -1 :)

WHEN 9 THEN (SELECT datepart(dw,getdate())-1 as day_of_week)

WHEN 10 THEN (SELECT convert(varchar, getdate(), 112) as yyymmdd)

WHEN 11 THEN (SELECT [dbo].[FormatDate] (CURRENT_TIMESTAMP,'YYMMDD') as yymmdd)

WHEN 12 THEN (SELECT [dbo].[FormatDate] (CURRENT_TIMESTAMP,'DDMMYYYY') as ddmmyyyy)

WHEN 13 THEN (SELECT [dbo].[FormatDate] (CURRENT_TIMESTAMP,'DDMMYY') as ddmmyy)

WHEN 14 THEN (SELECT [dbo].[FormatDate] (CURRENT_TIMESTAMP,'DDMM') as ddmm)

WHEN 15 THEN (SELECT [dbo].[FormatDate] (CURRENT_TIMESTAMP,'MMDD') as mmdd)

END

RETURN @returnValue;

END

The example of usage is:

CREATE PROCEDURE [test].[TestFunctionGetDateEntity]
AS
SET NOCOUNT ON
SELECT [dbo].[GetDateEntity] (0) as year;
SELECT [dbo].[GetDateEntity] (1) as month;
SELECT [dbo].[GetDateEntity] (2) as day;
SELECT [dbo].[GetDateEntity] (3) as quarter;
SELECT [dbo].[GetDateEntity] (4) as week;
SELECT [dbo].[GetDateEntity] (5) as hour;
SELECT [dbo].[GetDateEntity] (6) as minute;
SELECT [dbo].[GetDateEntity] (7) as second;
SELECT [dbo].[GetDateEntity] (8) as day_of_year;
SELECT [dbo].[GetDateEntity] (9) as day_of_week;
SELECT [dbo].[GetDateEntity] (10) as yyyymmdd;
SELECT [dbo].[GetDateEntity] (11) as yymmdd;
SELECT [dbo].[GetDateEntity] (12) as ddmmyyyy;
SELECT [dbo].[GetDateEntity] (13) as ddmmyy;
SELECT [dbo].[GetDateEntity] (14) as ddmm;
SELECT [dbo].[GetDateEntity] (15) as mmdd;

CREATE PROCEDURE [test].[TestFunctionGetDateEntity]

SET NOCOUNT ON

SELECT [dbo].[GetDateEntity] (0) as year;

SELECT [dbo].[GetDateEntity] (1) as month;

SELECT [dbo].[GetDateEntity] (2) as day;

SELECT [dbo].[GetDateEntity] (3) as quarter;

SELECT [dbo].[GetDateEntity] (4) as week;

SELECT [dbo].[GetDateEntity] (5) as hour;

SELECT [dbo].[GetDateEntity] (6) as minute;

SELECT [dbo].[GetDateEntity] (7) as second;

SELECT [dbo].[GetDateEntity] (8) as day_of_year;

SELECT [dbo].[GetDateEntity] (9) as day_of_week;

SELECT [dbo].[GetDateEntity] (10) as yyyymmdd;

SELECT [dbo].[GetDateEntity] (11) as yymmdd;

SELECT [dbo].[GetDateEntity] (12) as ddmmyyyy;

SELECT [dbo].[GetDateEntity] (13) as ddmmyy;

SELECT [dbo].[GetDateEntity] (14) as ddmm;

SELECT [dbo].[GetDateEntity] (15) as mmdd;

The result for this is something like this.

Don’t forget to create also the function “FormatDate” from the previous post.

Enjoy and feel free to modify it as you need. 🙂

Recent Posts

Categories