TSQL Logic test if DateTime2 variable has a Date

[Rx_]

Will the Statement cover for equal for an IsDate function in VBA?
if @Dt_APD_WithDrawn_Sub is not Null

The default is null. But, it is possible that a user enters a date, then highlights and deletes the date due to a mistake. This is part of a UDF.

Basically, the field needs to be checked to see if it contains a date or not.


Spent time looking up many post

		if @Dt_APD_WithDrawn_Sub is not Null
			Begin
				Set   @APDResult='Withdrawn' 
			end
		Else  -- With the two before completed - the multiple values run through a nested Case will determine the outcome
			Begin
				Set @Dt_APD_Sub =	(SELECT TOP 1 tblAPD_Fed_ST_CO.Dt_APD_Sub
									FROM tblAPD_Fed_ST_CO
									WHERE (((tblAPD_Fed_ST_CO.ID_Wells)=@ID_Wells) AND
									  (tblAPD_Fed_ST_CO.txtFedStCo)='FED')  AND (tblAPD_Fed_ST_CO.lngID_APD_Status In(1,2,3,4))
									ORDER BY tblAPD_Fed_ST_CO.Dt_APD_Sub DESC
									)

 

[WayneRyan]

Ex,

Don't both languages have IsDate ?

Wayne

 

[Rx_]

Yes, but the TSQL is more about evaluating text as a date.
The TSQL IsDate is notorious for evaluating bad dates as valid dates.
That is where VBA would tend to be much more newbie intuitive.
This is a quick example:

CREATE FUNCTION dbo.fnCheckDate
(@InDate nvarchar(50))
RETURNS DATETIME
AS
    BEGIN
        declare @Return DATETIME

        select @return = CASE WHEN ISDATE(@InDate) = 1
                            THEN CASE WHEN CAST(@InDate as DATETIME) BETWEEN '1/1/1901 12:00:00 AM' AND '6/6/2079 12:00:00 AM'
                                    THEN @InDate
                                    ELSE null
                                    END
                            ELSE null
                            END
        return @return
    END
GO

SELECT dbo.fnCheckDate('07/001/2012') --> Returns 2012-07-01 00:00:00.000
SELECT dbo.fnCheckDate('2012-07-002') --> Returns 2012-07-01 00:00:00.000
SELECT dbo.fnCheckDate('007/002/2012') --> Returns 2012-07-01 00:00:00.000
SELECT dbo.fnCheckDate('00/002/2012') --> Returns Null
SELECT dbo.fnCheckDate('006/031/2012') --> Returns Null
SELECT dbo.fnCheckDate('') --> Returns Null

 

[SQL_Hell]

The thing is though, unless you actually have dates in the format:

2012-001-001

Then ISDATE() should work for capturing non dates:

SELECT ISDATE('2012-01-01')

Returns 1

SELECT ISDATE('2012-13-33')

Returns 0


Your code looks familiar RX_, stackoverflow?

 

[Rx_]

Guilty as charged! I started finding many post about problems with IsDate listed on sqlservercentral.com forums. For your response, took the quick search for the stackoverflow example.

My concern was the table belonged to a past DBA that had no validation checks on the field. Users could highlight and enter an empty string. Later, I migrated the Access DB over to SQL Server. My stress increased reading about some of the issues people had with IsDate.

What I really needed to check was for Null to see if the field had a date or not.
Since the field type is now DateTime2 and the Access (via Linked Tables) front end enforces a date > FY 2000, I should be fine with checking null.

Your point is extremely well appreciated.
Making the conversions from VBA functions to T-sql functions has probably made me a little over cautious.

Here is an actual code function that worked in place of the IsDate.
If you have time, I would always appreicate a review.

Set @Dt_APD_Sub = (SELECT TOP 1 tblAPD_Fed_ST_CO.Dt_APD_Sub
         FROM tblAPD_Fed_ST_CO
         WHERE (((tblAPD_Fed_ST_CO.ID_Wells)=@ID_Wells) AND
           (tblAPD_Fed_ST_CO.txtFedStCo)='St')  AND (tblAPD_Fed_ST_CO.lngID_APD_Status In(1,2,3,4)) and (tblAPD_Fed_ST_CO.Dt_APD_Sub is Not Null)
         ORDER BY tblAPD_Fed_ST_CO.Dt_APD_Sub DESC
         ) --- Quality Assurance - Passed all records 4/22/2015 AM