how to convert string into datetime

[rizunb]

Hi People
i am trying to run this sql

strSQL = "select sum(BillableHours) as totalHours from TimeSheetHours, TimeSheet where TimeSheetHours.TimecardID = TimeSheet.TimecardID and TimeSheet.EmployeeID = " & empID & " and TimeSheet.MonthYear = '" & formonth & "'"

But its giving me an error saying datatype mismatch and i am 100% sure that error is bacause of the last condition "TimeSheet.MonthYear = '" & formonth & "'"
If i remove this condition my sql works fine

Now issue is that TimeSheet.MonthYear is a datetime field and the formonth variable is a string. I think thats the reason why its saying datatype mismatch

Can any one help me in getting this fixed
Thanks in advance
Rizzzzzzzz

 

[nealvenables]

Hi,

Try

"TimeSheet.MonthYear = '" & cast(formonth as datetime) & "'"

 

[rizunb]

Doesnt work
giving an error on "as"

 

[swampBoogie]

Which DBMS are you using?

 

[nealvenables]

D'oh!

This string is being constructed in VB or Acces VBA isn't it?

If Access, try:

"TimeSheet.MonthYear = '" & format(formonth, "dd mmm yyyy&quot & "'"

I don't know off-hand what the VB equivelent of Access' format function is, but if your code is running in VB use that instead.

 

[chmohan]

did u try cast(TimeSheet.MonthYear as string)= '" & formonth & "'"

 

[rizunb]

Still didnt worked
yes, i am using access
I tried cast(TimeSheet.MonthYear as string) = '" & formonth & "'

still didnt worked
One more thing the datetime field format is mm/yyyy
doesnt have dd in it but the formonth variable also contains mm/yyyy like "01/2000"

 

[SQLBill]

Post your query in the ACCESS forum. This is the Microsoft SQL Server forum. You'll get more accurate suggestions.

-SQLBill