SQL Format Date User Defined Function Error

Question

I have a user defined function that looks like this;

ALTER FUNCTION [dbo].[func_format_date]
(
    -- Add the parameters for the function here
    @input VARCHAR(6)
)
RETURNS VARCHAR(6)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @mon CHAR(2),
        @day char(2),
        @year char(4),
        @output DATETIME
 
    -- Return the result of the function
    SELECT @mon = LEFT(@input, 2), @day =SUBSTRING(@input, 3,2), @year = RIGHT(@input,4)
 
    SELECT @output = @year+@mon+@day
 
    RETURN CONVERT(VARCHAR(6), @output, 12)
END

The goal is to be able to pass in a date say “022019” (mmddyy) and the function will format the date as such “190220” (yymmdd). This seems to work only sometime as if I pass in the following dates see the results below; it seems to be inconsistent in terms of the dates it accepts vs the dates that throw an error

022019 (Works fine)
032019 (Works fine)
021019 (Results in a “The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.” error)
030519 (Results in a “The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.” error)

I have checked to ensure the default language is correct and it is. Can someone help me to figure this out?

Question your Year in your function is doing 4 digits, but your only passing 2 in the input? — Brad
– Brad, Commented Apr 18, 2019 at 14:42
What is 2 + 2 + 4? You defined your parameter as varchar(6). See any issues? — SMor
– SMor, Commented Apr 18, 2019 at 14:42
Almost 20 years after Y2K - no one should be using or accepting 2 digit years. NO ONE! — SMor
– SMor, Commented Apr 18, 2019 at 14:42
I tested and saw error, test your code without being in function, and change your output to varchar for testing so you can see what you will get from results. This way it wont error out, but will send you specific output, and you can see what is happening — Brad
– Brad, Commented Apr 18, 2019 at 14:44
Dates are binary values. They have no format. There's no mmddyy or yymmdd date, they are just dates. What's the point of this function? Why not just uses PARSE to parse whatever format is there into a date? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 18, 2019 at 14:58

Zhorov · Accepted Answer · 2019-04-18 16:45:23Z

1

First, the reason for this error - your @input parameter is varchar(6), you read 1st and 2nd characters as month, 3rd and 4th as day, and 3rd, 4th, 5th and 6th as year. When @input is equal to '021019', your year will be 1019 and this is outside the datetime datatype range.

Second, change your logic:

make your @input parameter varchar(8)
generate datetime value using DATETIMEFROMPARTS() function if you have four digit year

If you want to fix your function, next may help:

ALTER FUNCTION [dbo].[func_format_date]
(
   @input VARCHAR(6)
)
RETURNS VARCHAR(6)
AS
BEGIN
   -- Declare the return variable here
   DECLARE 
      @mon VARCHAR(2),
      @day VARCHAR(2),
      @year VARCHAR(2),
      @output DATETIME

   -- Return the result of the function
   SELECT 
      @mon = SUBSTRING(@input, 1, 2), 
      @day = SUBSTRING(@input, 3, 2), 
      @year = SUBSTRING(@input, 5, 2)

   -- Generate datetime value
   SELECT @output = CONVERT(datetime, @year + @mon + @day, 12)
   -- or if your dates are after 2000 
   --SELECT @output = DATETIMEFROMPARTS(2000 + @year, @mon, @day, 0, 0, 0, 0)

   -- Return value
   RETURN CONVERT(VARCHAR(6), @output, 12)
END

edited Apr 18, 2019 at 16:45

answered Apr 18, 2019 at 14:53

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Panagiotis Kanavos Over a year ago

Assuming 2000 for a base year is wrong. That would convert 99 to 2099. SQL Server's configurable cutoff is 2049.

Zhorov Over a year ago

@PanagiotisKanavos You are right, DATETIMEFROMPARTS is usefull with four digit years and it's not sure that dates will be after 2000. Thanks.

Arif Shaikh · Accepted Answer · 2019-04-18 15:28:00Z

0

You Can Simply your function using below Stmt.

If no Date Validation Required you can interchange values. select RIGHT(@input,2) + LEFT(@input, 2) + SUBSTRING(@input, 3,2)
If Date Validation Required use below. select Convert(varchar, Convert(datetime, LEFT(@input, 2) + '/' + SUBSTRING(@input, 3,2) + '/' + RIGHT(@input,2), 1), 12)

answered Apr 18, 2019 at 15:28

Arif Shaikh

11 bronze badge

Collectives™ on Stack Overflow

SQL Format Date User Defined Function Error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related