ASP type mismatch with a date 2

[mfenn]

Hello all,

Im pulling a date out of SQL Server that is 04141991 format and i need to change it to 19910414. I'm pretty new to ASP and I inherited this app from the previous IT person.


strSql= " Select * from enrolldep where enrollid = '" & strEnrollID & "'"
Set rst2 = dbConn.execute(strSql )
reccount = 15
do while not rst2.EOF

reccount = reccount + 1

strDepLastName = trim(rst2.Fields("DepLastName"))
strDepFirstName = trim(rst2.Fields("DepFirstName"))
strDepSSN = trim(rst2.Fields("DepSSN"))
strDepMI = trim(rst2.Fields("DepMiddleInitial"))
strDepRelation = trim(rst2.Fields("DepRelation"))
strDepGender = trim(rst2.Fields("DepGender"))
strDepDOB = trim(rst2.Fields("DepDOB"))
straddordel = trim(rst2.Fields("addordel"))

'need to convert the date to yyyymmdd for the dep dob
if Len(trim(strDepDOB)) > 1 then
strYear = Year(strDepDOB)
strMonth = Month(strDepDOB)
if len(strMonth) = 1 then
strMonth = "0" & strMonth
end if
strDay = day(strEmpDOB)
if len(strDay) = 1 then
strDay = "0" & strDay
end if
strFormatedDepDOB = strYear & strMonth & strDay
End IF

Then we have the response.write stuff to display the variables on the webpage and the loop.

The error shows up for this line of the conversion: strYear = Year(strDepDOB)
Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: '[string: "04141991"]'
/app/companywide/onlineenrollment/PendEmp.asp, line 1321

The odd thing is, I have this exact same code earlier in the page to format another date and it works perfectly.

Any help would be much appreciated!

Mel

 

[guitarzan]

If the input is always in mmddyyy format, you can do something like this:

if  Len(trim(strDepDOB)) > 1 then 
   strDay = left(strDepDOB, 2)
   strMonth = mid(strDepDOB, 3, 2)
   strYear = right(strDepDOB, 4)

   strFormatedDepDOB = strYear & strMonth & strDay
End IF

 

[mfenn]

Hi,

The dates are always in mm/dd/yyyy, but sometimes the month and day are not 2 digits (3/4/2010) which breaks the above code.

I'll give this a try, it should work for 98% of the data.

Thanks for the help.

 

[gmmastros]

Can you explain for us how the data is stored in the database. Specifically, what is the data type? VarChar(8)? Also, can you show some sample data from the table (just for the date column).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[guitarzan]

... and whether or not there are /'s in the data or not!

 

[foxbox]

<%
dim cDateString
cDateString = "31/12/2010"  ' dd/mm/yyyy

' On what position is the 1st /?
dim nFirstSlash
nFirstSlash = instr(cDateString,"/")

' On what position is the 2nd /?
dim nSecondSlash
nSecondSlash = instr(nFirstSlash+1, cDateString,"/")

response.Write "Day = " & left(cDateString,nFirstSlash-1) & "<br>"
response.Write "Month = " & mid(cDateString,nFirstSlash+1,nSecondSlash-nFirstSlash-1)& "<br>"
response.Write "Year =" & right(cDateString,4)
%>

 

[foxbox]

Sorry, i tested that with long day/month too. But this works:

<%
dim cDateString
cDateString = "3/4/2010"  ' dd/mm/yyyy

' On what position is the 1st /?
dim nFirstSlash
nFirstSlash = instr(cDateString,"/")

' On what position is the 2nd /?
dim nSecondSlash
nSecondSlash = instr(nFirstSlash+1, cDateString,"/")

response.Write "Day = " & left(cDateString,nFirstSlash-1) & "<br>"
response.Write "Month = " & mid(cDateString,nFirstSlash+1,nSecondSlash-nFirstSlash-1)& "<br>"
response.Write "Year =" & right(cDateString,4)
%>

 

[mfenn]

Thanks for the responses.

The dates are char(10) and the /'s are always present in the dates for this app. There are several dates fields in this table that have these issues.

some examples from the data:

2/10/2010
2/8/2001
02/28/1988
4/6/10
04/06/83

I can change the external site, where the data comes from, so the dates are consistantly MM/DD/YYYY but that doesn't help with the existing dates.

I'll give your code a try foxbox and let y'all know how it works.

 

[guitarzan]

The statement...

the /'s are always present in the dates for this app

is not supported by

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: '[string: "04141991"]'
/app/companywide/onlineenrollment/PendEmp.asp, line 1321

I can only assume that you have some data that is not a valid date string (for example, "04141991"), and that is causing your problems. If this is true, foxbox's code will not help. You should use the IsDate() function to verify that you are dealing with a valid date string. If it's valid, foxbox's suggestion should work (and for that matter, your original code should work too!) If it is NOT a valid date, you will have to determine how to fix it.

 

[mfenn]

Thanks!

This is now working, appreciate the help.

 

[wvdba]

you can also simplify it by doing this:
mm = month(the_date)
dd = day(the_date)
yy = year(the_date)
then you will have all 3 items.