How to check if a column is in valid date format

sheema · Post by **sheema** » Tue Jan 09, 2007 10:42 am

My source is a sequential file,where in i have a column which is of type varchar and length is 8.I need to check whether this column is a date in the format YYYYMMDD.If it is not in that format i need to replace a Null.

Do we have any function to check this.

Thanks
sheema

kcbland · Post by **kcbland** » Tue Jan 09, 2007 10:45 am

Yes, you can search the forum and find custom functions folks have written, or write your own using ICONV and OCONV and ISNULL and other BASIC functions.

DSguru2B · Post by **DSguru2B** » Tue Jan 09, 2007 10:46 am

Nothing out of the box but its no sweat. Do a substring and check for your limits. I know MM will be greater than 0 and less than 13. Similarly DD will be between 1 and 31, inclusive. You define YYYY. What is a valid date for you. Do a check on that.
If you want to go into more depth, then you can also check if YYYY is a not a leap year and MM is 02 then DD cannot be greater than 28.

DSguru2B · Post by **DSguru2B** » Tue Jan 09, 2007 10:46 am

Or go the ICONV/OCONV way as Ken suggests

us1aslam1us · Post by **us1aslam1us** » Tue Jan 09, 2007 10:47 am

you the len() function and check the length of the field.

Code: Select all

If len(dslink.A)=8 then dslink.A else 'Null'

Oh i am sorry I thought OP just want to check it.

sheema · Post by **sheema** » Tue Jan 09, 2007 11:07 am

How do i use IConv and OCONV to check this and convert into correct format.

Thanks

sheema

DSguru2B · Post by **DSguru2B** » Tue Jan 09, 2007 11:11 am

Check this link out.

sheema · Post by **sheema** » Tue Jan 09, 2007 11:24 am

Thanks,I am not able to see the premium content.

Any help is appreciated.

Thanks
sheema

DSguru2B · Post by **DSguru2B** » Tue Jan 09, 2007 11:27 am

Check again. I made them visible for now. You are 25 posts into your membership. You should really consider becoming a premium member as you get routines written by others and basically solution to your problems in a silver platter.

sheema · Post by **sheema** » Tue Jan 09, 2007 11:33 am

No,I cannot see the premium content.

Thanks
sheema

DSguru2B · Post by **DSguru2B** » Tue Jan 09, 2007 11:44 am

You cannot see this viewtopic.php?t=89024&highlight=check+for+valid+date

chulett · Post by **chulett** » Tue Jan 09, 2007 11:49 am

How could they? It's all converted Premium Content from the switchover.

sheema · Post by **sheema** » Tue Jan 09, 2007 11:59 am

I want to do this using Stagevariables.

In first stage variable Stage1 i use IConv(Date,"DYMD[4,2,2]")

Now i want to check the status of the above stage variable and do an OConv().Can you guide me how to do this.

Thanks
sheema

chulett · Post by **chulett** » Tue Jan 09, 2007 12:17 pm

You can only check the status if you do all that in a Routine, which can then be used to populate your stage variables if you like.

sheema · Post by **sheema** » Tue Jan 09, 2007 12:26 pm

Is there any way that i can validate the date through stage variables.

Thanks
sheema