Date Problem

[dparikh]

When i select dates through cal in my application formate is 1/1/2001 . In my database date col is string. My SQL query is like this
select c.checkdate,c.grosspay, c.amountofcheck from empcheckinfo c,empdetailinfo e
where e.ssn = '111-11-111'
and e.empnumber = c.empnumber
and right(c.checkdate,4) + left(c.checkdate,4) between '1/1/2001' and '12/31/2001'
I get this result in debug.print sql (when i runt this query in sql i dont get any records ) But if i do
select c.checkdate,c.grosspay, c.amountofcheck from empcheckinfo c,empdetailinfo e
where e.ssn = '111-11-111'
and e.empnumber = c.empnumber
and right(c.checkdate,4) + left(c.checkdate,4) between '01012001' and '12312001'
I get all the records. HOw do i slove this problem .
Thanks in Advance

 

[RalphBrown]

If you are referring to your date field as "right(c.checkdate,4) + left(c.checkdate,4) " you need to have the year first in the string following the "between", e.g. '20011/1/'. You are also not allowing for the fact that your month & day strings can be 1 or 2 chrs long.

Hope this helps,
Ralph

 

[dparikh]

But Data in my tables stores date as char and formate is 12311998. I am not sure how do i change the formate.
Thanks for your help

 

[zemp]

If you are ghetting the records that you want whyu change the format of the data in the database? Instead why notwrite a small function that converts the string into a readable date when you deal with the recordset or display the date for the user.

If you know the format is always ddmmyyyy then you couls write a function like the one below to convert the strings to a useable date.

Private Function ToDate(pString) As Date
Dim NewDate As String

NewDate = Left(pString, 2) & "/" & Mid(pString, 3, 2) & "/" & Right(pString, 4)
ToDate = CDate(NewDate)
End Function
Thanks and Good Luck!

zemp