SQL Server Date Conversion Function


Here we have a couple of fairly simple code examples that work with dates. The first one gets today’s date and converts it to a different format. It also does a little addition to get tomorrows date and displays it. The second code example works with Julian dates.

USE myDatabase
GO
/*   Using dates - Example functions
Here we get today's date and time from the system.
We can extract the year, month and day, reformat and display.

SQL Server Functions Used: getdate(), cast(), datepart() 
substring() and dateadd()
*/
DECLARE @datetoday as smalldatetime
DECLARE @datetomorrow as smalldatetime
DECLARE @YearString as varchar(4)   /* i.e. 2017 */
DECLARE @MonthString as varchar(2)
DECLARE @DayString as varchar(2)

SET @datetoday = getdate()
SET @YearString = cast(DATEPART(yyyy, @datetoday) as varchar(4));
SET @MonthString = cast(DATEPART(mm, @datetoday) as varchar(2));
SET @DayString = cast(DATEPART(dd, @datetoday) as varchar(2));

DECLARE @yy_mm_dd as varchar(8)
SET @yy_mm_dd = SUBSTRING(@YearString,3,2) + '-' +  @MonthString + '-' + @DayString
PRINT 'Today''s date in this format (yy-m-d) is: ' + @yy_mm_dd

-- go back to the date-type variable to add one day, do not simply 
-- add one day to the day string. That will eventually bite you.
SET @datetomorrow = DATEADD(DAY,1,@datetoday)
-- SET @datetoday = @datetomorrow -1
SET @YearString = cast(DATEPART(yyyy, @datetomorrow) as varchar(4));
SET @MonthString = cast(DATEPART(mm, @datetomorrow) as varchar(2));
SET @DayString = cast(DATEPART(dd, @datetomorrow) as varchar(2));
SET @yy_mm_dd = SUBSTRING(@YearString,3,2) + '-' +  @MonthString + '-' + @DayString
PRINT 'Tomorrow''s date in this format (yy-m-d) is: ' + @yy_mm_dd


Here is a T-SQL ufunction that converts a date format from Julian format to ddmmyy format. Both dates are strings.

USE [myDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Mike
-- Create date:         Feb. 7, 2017 (2017038)
-- Description:	        Converts date format
-- =============================================
CREATE FUNCTION [dbo].[JulianToDDMMYY01] (@JulianIncomingDate varchar(7)) 
RETURNS varchar(6)
AS
BEGIN
	DECLARE @ddmmyyChar6 as varchar(6)  -- Declare the return variable
	DECLARE @DatePaymentDate as date
	DECLARE @YearString as varchar(4)   /* i.e. 2017 */
	DECLARE @MonthString as varchar(2)
	DECLARE @DayString as varchar(2)

	-- We need to convert incoming @JulianIncomingDate in Julian format to char(6) DDMMYY
	-- convert to date type, use DATEPART() function to extract

	/* Convert Julian varchar string to a date-type variable */
	SET @DatePaymentDate = DATEADD(YEAR, (@JulianIncomingDate  / 1000) - 1900, @JulianIncomingDate  % 1000 - 1)

	/* Convert date-type @DatePaymentDate into 3 varchar strings, then concatenate */
	SET @YearString = cast(DATEPART(yyyy, @DatePaymentDate) as varchar(4));
	SET @MonthString = cast(DATEPART(mm, @DatePaymentDate) as varchar(2));
	SET @DayString = cast(DATEPART(dd, @DatePaymentDate) as varchar(2));
	SET @ddmmyyChar6 = right('00' + @DayString,2)
			+ right('00' + @MonthString,2)
			+ right(@YearString,2);
	RETURN @ddmmyyChar6
END;
GO



Leave a comment

Your email address will not be published. Required fields are marked *