Function chokes or zero or NULL value 1

[dgillz]

I have a database that stores its dates as a number in a YYYYMMDD format. I have created a function to convert this to a real date, however the function fails if the date is zero or NULL. Here is the function:

CREATE FUNCTION RealDate (@date_in INT) 
RETURNS datetime
AS
BEGIN
   DECLARE @date_out datetime
   SET @date_out = CONVERT(datetime, CAST(@date_in AS CHAR(8)), 101)

   RETURN @date_out
END
GO

Any idea what I can do to get this to return a date of say 1/1/1900 is the database value is zero or Null?

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[gmmastros]

Try this:

ALTER FUNCTION dbo.RealDate(@date_in INT) 
RETURNS datetime
AS
BEGIN
   Return (Select Coalesce(CONVERT(datetime, Case When IsDate(CAST(@date_in AS CHAR(8))) = 1 Then CAST(@date_in AS CHAR(8)) Else NULL End, 101), 0))
END

Your original function had more problems other than 0. In fact, any date less than Jan 1, 1753 would have caused an error. Other "numbers" would also have caused an error, Like Feb 29, 2010 (2010 is not a leap year).

The code replacement I suggest converts to char, and then uses the IsDate function to determine if the date is valid. Any invalid date will return Jan 1, 1900.

Ex:

Select dbo.RealDate(NULL)
Select dbo.RealDate(0)
Select dbo.RealDate(20100229)

Also notice that your original function declared a variable, set it, and then returned it. I removed those bits of code, so this function should now run a tiny bit faster than the previous version (except now there is an IsDate check so it may actually run slower, but without the errors).

Make sense?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dgillz]

Many thanks, that makes perfect sense. I was unaware of the COALESCE() function.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[gmmastros]

Are you aware of the IsNull function? Coalesce is very similar to IsNull, but there are some differences. In this situation, it is used to convert NULL to 0, which is implicitly converted to DateTime.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dgillz]

I was aware of ISNULL but I like COALESCE better now that I have read up on it, at least for this purpose.

In theory, due to the the application controlling the user's data input, you would not be able to have an invalid value like 20100229 or just a number like 5, but I really like testing for that in my code.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[gmmastros]

In theory

I know what you mean. You never know when data is gonna get dumped in to the DB from a source outside your application. Spending an extra CPU cycle here and there won't affect performance, but may prevent bugs later.

Better safe than sorry.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[flapeyre]

I have a database that was ported from an AS/400, which had the dates stored as CYYMMDD (numeric) and also some of the dates had a separate time field.

Here is a function I wrote to return a DATETIME field from that data:

/****** Object:  UserDefinedFunction [dbo].[fn_CYYMMDD_to_datetime]    Script Date: 08/15/2011 13:04:37 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_CYYMMDD_to_datetime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_CYYMMDD_to_datetime]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_CYYMMDD_to_datetime]    Script Date: 08/15/2011 13:04:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[fn_CYYMMDD_to_datetime]
  (
    @i_CYYMMDD DECIMAL(7, 0)
  , @i_Time DECIMAL(6, 0)
  )
RETURNS DATETIME
AS 
    BEGIN
      DECLARE
        @p_DateOut DATETIME
      , @w_time CHAR(6)
      , @w_hh DECIMAL(2, 0)
      , @w_mm DECIMAL(2, 0)
      , @w_ss DECIMAL(2, 0)


      SELECT
          @p_DateOut = CASE WHEN ISDATE(@i_CYYMMDD + 19000000) = 1
                            THEN CAST(CAST(@i_CYYMMDD + 19000000 AS CHAR(8)) AS DATETIME)
                            ELSE NULL
                       END
                       
-- Add time (if specified)
      IF @p_DateOut IS NOT NULL
        AND @i_Time IS NOT NULL
        AND @i_Time BETWEEN 000000 AND 235959 
        BEGIN
          SELECT
              @w_time = REPLICATE('0', 6 - LEN(CAST(@i_time AS VARCHAR(6))))
              + CAST(@i_time AS CHAR(6))
          SELECT
              @w_hh = CAST(SUBSTRING(@w_time, 1, 2) AS DECIMAL(2, 0))
          SELECT
              @w_mm = CAST(SUBSTRING(@w_time, 3, 2) AS DECIMAL(2, 0))
          SELECT
              @w_ss = CAST(SUBSTRING(@w_time, 5, 2) AS DECIMAL(2, 0))
          SELECT
              @p_dateout = DATEADD(hour, @w_hh, @p_DateOut)
          SELECT
              @p_dateout = DATEADD(minute, @w_mm, @p_DateOut)
          SELECT
              @p_dateout = DATEADD(second, @w_ss, @p_DateOut)
        END
            
	-- Return the result of the function
      RETURN @p_DateOut  
      
    END


GO

-- Francis
The brain is a wonderful organ; it starts working the moment you get up in the morning, and does not stop until you get to the office.
--Robert Frost