How to check if a column is in valid date format
Moderators: chulett, rschirm, roy
How to check if a column is in valid date format
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
Do we have any function to check this.
Thanks
sheema
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
you the len() function and check the length of the field.
Oh i am sorry I thought OP just want to check it.
Code: Select all
If len(dslink.A)=8 then dslink.A else 'Null'
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
Thomas Alva Edison(1847-1931)
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
You cannot see this viewtopic.php?t=89024&highlight=check+for+valid+date
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.