ifgtest001
asked on
Date Verifier in VB
In SQL there is a command ISDATE(YYMMDD) which returns 1 if the parameter is a date and 0 if it is not a valid date.
I am looking for a built in Visual Basic function which will allow me to pass in a 6 character field and return either true or false depending on whether or not the string is a valid date.
Here are a few examples
"770705" => True, This is July 5, 1977
"840010" => False, there is no Month
"000101" => True, This is January 1, 2000
"01B0pH" => False, This is garbage data
If any one can tell me the VB equivalant to the SQL isdate command, I will reward them the points. It is that simple...
Thanks
I am looking for a built in Visual Basic function which will allow me to pass in a 6 character field and return either true or false depending on whether or not the string is a valid date.
Here are a few examples
"770705" => True, This is July 5, 1977
"840010" => False, there is no Month
"000101" => True, This is January 1, 2000
"01B0pH" => False, This is garbage data
If any one can tell me the VB equivalant to the SQL isdate command, I will reward them the points. It is that simple...
Thanks
try that:
exaple
isdate(format("980505","yy
exaple
isdate(format("980505","yy
Beat you VK! It doesn't actually matter what the seperator is as long as it is a valid date seperator somewhere.
Is this really correct??? I'm trying this in Excel VBA, but it really doesn't work the way you imply.
Please correct me if I'm wrong...
Try this in the immediate window:
?format("980505","yyyy/dd/
It really doesn't show us the date 1998-05-05!
It shows us the date that is 980505 days from January 1 1900 - which is 4584-11-07.
To verify that "980505" is a date, you must insert separators and check if it's a date. This will work for a 6-digit YYMMDD date:
function isitadate(d as string) as boolean
isitadate = isdate(mid(d,1,2) & "-" & mid(d,3,2) & "-" & mid(d,5,2))
end function
Ture Magnusson
Karlstad, Sweden
Please correct me if I'm wrong...
Try this in the immediate window:
?format("980505","yyyy/dd/
It really doesn't show us the date 1998-05-05!
It shows us the date that is 980505 days from January 1 1900 - which is 4584-11-07.
To verify that "980505" is a date, you must insert separators and check if it's a date. This will work for a 6-digit YYMMDD date:
function isitadate(d as string) as boolean
isitadate = isdate(mid(d,1,2) & "-" & mid(d,3,2) & "-" & mid(d,5,2))
end function
Ture Magnusson
Karlstad, Sweden
>In SQL there is a command ISDATE(YYMMDD) which returns 1
>if the parameter is a date and 0 if it is not a valid date.
You might want to clarify which SQL environment you are speaking. The syntax, keywords, and functions differ from one DBMS to another. You don't do things exactly the same way in MS Access SQL as you do in MS SQL Server, or in Oracle SQL.
Ture is correct in the fact that the first couple of suggestions would return a date, but not the one you expected.
-Dennis Borg
>if the parameter is a date and 0 if it is not a valid date.
You might want to clarify which SQL environment you are speaking. The syntax, keywords, and functions differ from one DBMS to another. You don't do things exactly the same way in MS Access SQL as you do in MS SQL Server, or in Oracle SQL.
Ture is correct in the fact that the first couple of suggestions would return a date, but not the one you expected.
-Dennis Borg
I didn't actually test that the date determined was what it should have been, thanks ture for the extra mile.
... or the extra instruction: FormatDateTime(mydate, date_format) where date_format would be vbShortDate in the above examples.
Adding to previous comments, Try this:
Private Function CheckIfDate(MyDate As String)
Dim sDate As String
MyDate = "770705"
sDate = Left(MyDate,2) & "-" & Mid(MyDate,3,2) & "-" & Right(MyDate, 2)
CheckIfDate = IsDate(sDate)
End Sub
'Hope this helps.
Private Function CheckIfDate(MyDate As String)
Dim sDate As String
MyDate = "770705"
sDate = Left(MyDate,2) & "-" & Mid(MyDate,3,2) & "-" & Right(MyDate, 2)
CheckIfDate = IsDate(sDate)
End Sub
'Hope this helps.
? IsDate(Format("981005", "@@/@@/@@"))
True
' insert / characters
?Format("981005", "@@\/@@\/@@")
98/10/05
' insert date separators
?Format("981005", "@@/@@/@@")
98/10/05
True
' insert / characters
?Format("981005", "@@\/@@\/@@")
98/10/05
' insert date separators
?Format("981005", "@@/@@/@@")
98/10/05
ifgtest001:
Here is an example illustrating how you can perform your check:
IsDate(Format("770705","00
This will return either true or false
The only "thing" would be if you had dates such as the following:
"010203"
Which should it be:
Jan 02, 2003
Feb 01, 2003
Feb 03, 2001
etc.
It is a little ambiguous, but doesn't change the fact of whether or not it is a valid date.
-Dennis Borg
Here is an example illustrating how you can perform your check:
IsDate(Format("770705","00
This will return either true or false
The only "thing" would be if you had dates such as the following:
"010203"
Which should it be:
Jan 02, 2003
Feb 01, 2003
Feb 03, 2001
etc.
It is a little ambiguous, but doesn't change the fact of whether or not it is a valid date.
-Dennis Borg
Hi ifgtest001,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:
Save as PAQ -- No Refund.
*** Too many excellent answers; too few points to split
ifgtest001, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit. Explain why.
==========
DanRollins -- EE database cleanup volunteer
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:
Save as PAQ -- No Refund.
*** Too many excellent answers; too few points to split
ifgtest001, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit. Explain why.
==========
DanRollins -- EE database cleanup volunteer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
IsDate("770705") = False
IsDate("77-07-05") = True
But all is not lost, you can easily convert the 6 character string into an acceptable date format using:
IsDate(format("770705","yy