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).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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 |
The example of usage is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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; |
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. 🙂