Error in Query

[hafizfarooq]

Hello i trying to get some results from my tables between 2 dates using VB 6 adn SQL Server 2000.

i wrote the following query for this

MDateQry = " BETWEEN '" & TxtDtFrom & "' And '" & TxtDtTo & "'"

RsGlt.Open "Select Code,Tdate from glt where Code = " & RsDrt.Fields("Code") & "  and tDate" & MDateQry, Cn, adOpenStatic, adLockOptimistic

but ig gives the error :

[Microsoft][odbc sql server driver][sql server] The Conversion of a char Data type to a datetime data type resulted in a out-of-range date time value.

What is this problem?
Please help me

[zeezee]

What is the date Format you are passing to the querry? It should be MM/dd/yyyy.

[zynder]

Try putting a parenthesis in case your confused.

"SELECT Code, Tdate FROM glt WHERE (tDate BETWEEN '" & TxtDtFrom & "' AND '" & TxtDtTo & "') " & _
             "AND (Code = " & RsDrt.Fields("Code").Value & ")"

[randem]

Try

sSql = "Select Code,Tdate from glt where Code = " & RsDrt.Fields("Code") & "  and tDate" & MDateQry
Debug.Print sSql

Occam's Razor...

[Comintern]

Use date qualifiers (#) instead of text qualifiers ('). Also, make sure your inputs are valid dates. Finally, you should get in the habit of capitalizing SQL key words to make it easier to read and always end your statements with ";".

If IsDate(TxtDtFrom) And IsDate(TxtDtTo) Then
    MDateQry = "BETWEEN #" & TxtDtFrom & "# And #" & TxtDtTo & "#"
    RsGlt.Open "SELECT Code,Tdate FROM glt WHERE (Code = " & RsDrt.Fields("Code") & ") " & _
               "AND (tDate " & MDateQry & ");"
Else
    Debug.Print "Invalid input"
End If

[zynder]

Use date qualifiers (#) instead of text qualifiers ('). Also, make sure your inputs are valid dates. Finally, you should get in the habit of capitalizing SQL key words to make it easier to read and always end your statements with ";".

If IsDate(TxtDtFrom) And IsDate(TxtDtTo) Then
    MDateQry = "BETWEEN #" & TxtDtFrom & "# And #" & TxtDtTo & "#"
    RsGlt.Open "SELECT Code,Tdate FROM glt WHERE (Code = " & RsDrt.Fields("Code") & ") " & _
               "AND (tDate " & MDateQry & ");"
Else
    Debug.Print "Invalid input"
End If

I don't agree with this. He is using SQL Server 2000 not Access. Using (#) will return an error. The correct way is using (') for BETWEEN statement. Correct me if im wrong buddy.

[Hack]

Correct me if im wrong buddy.

You are right.

[RazorT]

Anytime I deal with dates in SQL I ALWAYS use Format("YYYYMMDD") to guarentee consistency regardless of platform settings. This has the added advanctage of making sure its always a string so the Between will always work with " ' "

I've been stung too many times to risk leaving it to chance!!

[zynder]

Thank you. Sometimes we should double check what we post or read the problem details first before posting. It may not be our intention but we could mislead them with wrong advice.

[hafizfarooq]

Thankyou ALL.

[hafizfarooq]

How to use CAST function in QUERY using SQL SERVER and VB