IsValidDate function not returning 1 or 0

[adi_2_chaos]

Hi All,

I have an input column of Varchar datatype which can have two formats:
ddmmyyyy or yyyymmdd. So to be able to process both types of records, I use the function

Code: Select all

If IsValidDate(StringToDate(StageVar, "%yyyy%mm%dd")) Then StringToDate(StageVar, "%yyyy%mm%dd")
Else
StringToDate(StageVar, "%dd%mm%yyyy")

But while trying to implement the above (for a sample input 04102010), the records get dropped. Upon investigation, I have concluded that this is happening since the function IsValidDate(StringToDate(StageVar, "%yyyy%mm%dd")) is returning NULL (instead of an expected 0), and the record's getting dropped. Also, I get the following warning:

Conversion error calling conversion routine date_from_string data may have been lost

Can anyone please guide me on this

thanks
sriram

[pandeesh]

this should definitely throw error: :D

Code: Select all

If IsValidDate(

The first argument for IsValid() is the datatype you are going to validate and the second one is just string representation(so no need to convert to date before validating).

[HendrikB]

Hi,
apart from a proper function name and syntax, is there (really) no need to validate input data in format "%dd%mm%yyyy" as well?
You could get data like 29022011 or 00102010 which would generate error logs ...

[dsusersaj]

IsValid = Return whether the given string is valid for the given type

I think you need to give data type format as well as you gave. Have you made sure that no NULL value gets into the stage variable?.

[chulett]

The lesson to take away from this is to first check to see if your string is valid and then convert it only if it is in fact valid. Converting and then checking is too late.