Date Problem in Visual Basic 2005 and Sql Server 2005

[umeshj99]

Hi!

I am using SQL Server 2005 express edition as backend and Visual Basic 2005 express edition as frontend. This question is related to date comparison.

SELECT IssueDate, Client, AgencyCode
FROM SPACEBOOKING
WHERE (IssueDate BETWEEN CONVERT(DATETIME, '2006-05-01 00:00:00', 102) AND CONVERT(DATETIME, '2006-05-31 00:00:00', 102)) AND (AgencyCode = 1595)
ORDER BY IssueDate


Above query is generated by query designer and it retrieves 16 records.

However, when I use this code in my vb 2005 application and instead of '2006-05-01 00:00:00', I use dtpicker1.value and instead of '2006-05-31 00:00:00' I use dtpicker2.value, it gives me the following error :

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

What is the problem and how to solve it?

My regional date setting id dd/mm/yyyy.

[Killer42]

I'd suggest that you try plugging "mm/dd/yyyy" format into the SQL.

See this link for more information.

By the way, thanks for providing so much detail about your circumstances. It makes things a lot easier.

[umeshj99]

I'd suggest that you try plugging "mm/dd/yyyy" format into the SQL.

See this link for more information.

By the way, thanks for providing so much detail about your circumstances. It makes things a lot easier.

Thanks! But please tell me how to plug "mm/dd/yyyy" format into the SQL. Because in VB 2005 code, I am not giving any format. I am just using Convert function with dtpicker1.value and dtpicker2.value.

One more observation is that in VB 2005 code, if I use 103 style instead of 102, then it doesn't give the error, but it gives wrong results.(I think style 103 corresponds to my regional date settings).

To be precise, style 102 gives correct results when executed from SQL Server 2005 Management Studion Express, but gives error when executed from VB 2005 Code. On other hand, style 103 gives error when executed from SQL Server 2005 Management Studion Express, and gives wrong results when executed from VB 2005 code.

Please help.

[Killer42]

Thanks! But please tell me how to plug "mm/dd/yyyy" format into the SQL. Because in VB 2005 code, I am not giving any format. I am just using Convert function with dtpicker1.value and dtpicker2.value.

One more observation is that in VB 2005 code, if I use 103 style instead of 102, then it doesn't give the error, but it gives wrong results.(I think style 103 corresponds to my regional date settings).

To be precise, style 102 gives correct results when executed from SQL Server 2005 Management Studion Express, but gives error when executed from VB 2005 Code. On other hand, style 103 gives error when executed from SQL Server 2005 Management Studion Express, and gives wrong results when executed from VB 2005 code.

Please help.

Ok, I'm really a bit out of my depth here, as you're getting into the innards of VB.Net and I'm only a VB6 user (I really should update, but can't find the time).

In VB6, I would just use Format() function to format the date/time value. I don't think I've ever even heard of the CONVERT function, so can't advise you on that. But from what you said about 102 & 103, perhaps there's another code which refers to the U.S. format. I hope you referred to the link I gave you, as it provides details about the # delimiters and so forth.

Keep in mind that from here on I'm dealing in pure guesswork, so take it with a grain of salt...

Another possible source of error is that you have included the actual control name in the SQL string. For example, let's assume your SQL string is as follows...

SELECT IssueDate, Client, AgencyCode
FROM SPACEBOOKING
WHERE (IssueDate BETWEEN CONVERT(DATETIME, DatePicker1.Value, 102) AND CONVERT(DATETIME, DatePicker1.Value, 102)) AND (AgencyCode = 1595)
ORDER BY IssueDate

In that case, I would say it's very probably wrong. Because the SQL interpreter (in the database, not VB) probably has no idea what "DatePicker1.Value" means. It's just a string, and doesn't mean anything as a date. In this case you would need to concatenate the actual value from the DatePicker control into the string, not the property name. This is the age-old problem of differentiating between a variable and its value. You may think you had that one figured out ages ago, but SQL tends to complicate it further.

The code is likely to be something along these lines...

strSQL = "SELECT IssueDate, Client, AgencyCode " & _
    "FROM SPACEBOOKING " & _
    "WHERE (IssueDate BETWEEN #" & _
    Format(DatePicker1.Value, "mm/dd/yyyy") & _
    "# AND #" & _
    Format(DatePicker2.Value, "mm/dd/yyyy") & _
    "#) AND (AgencyCode = 1595) " & _
    "ORDER BY IssueDate"[/b]

Which should result in a string something like... (without the line-wrapping)

SELECT IssueDate, Client, AgencyCode
FROM SPACEBOOKING
WHERE (IssueDate BETWEEN #05/01/2006# AND #05/31/2006#)
AND (AgencyCode = 1595)
ORDER BY IssueDate

[umeshj99]

Ok, I'm really a bit out of my depth here, as you're getting into the innards of VB.Net and I'm only a VB6 user (I really should update, but can't find the time).

In VB6, I would just use Format() function to format the date/time value. I don't think I've ever even heard of the CONVERT function, so can't advise you on that. But from what you said about 102 & 103, perhaps there's another code which refers to the U.S. format. I hope you referred to the link I gave you, as it provides details about the # delimiters and so forth.

Keep in mind that from here on I'm dealing in pure guesswork, so take it with a grain of salt...

Another possible source of error is that you have included the actual control name in the SQL string. For example, let's assume your SQL string is as follows...

SELECT IssueDate, Client, AgencyCode
FROM SPACEBOOKING
WHERE (IssueDate BETWEEN CONVERT(DATETIME, DatePicker1.Value, 102) AND CONVERT(DATETIME, DatePicker1.Value, 102)) AND (AgencyCode = 1595)
ORDER BY IssueDate

In that case, I would say it's very probably wrong. Because the SQL interpreter (in the database, not VB) probably has no idea what "DatePicker1.Value" means. It's just a string, and doesn't mean anything as a date. In this case you would need to concatenate the actual value from the DatePicker control into the string, not the property name. This is the age-old problem of differentiating between a variable and its value. You may think you had that one figured out ages ago, but SQL tends to complicate it further.

The code is likely to be something along these lines...

strSQL = "SELECT IssueDate, Client, AgencyCode " & _
    "FROM SPACEBOOKING " & _
    "WHERE (IssueDate BETWEEN #" & _
    Format(DatePicker1.Value, "mm/dd/yyyy") & _
    "# AND #" & _
    Format(DatePicker2.Value, "mm/dd/yyyy") & _
    "#) AND (AgencyCode = 1595) " & _
    "ORDER BY IssueDate"[/b]

Which should result in a string something like... (without the line-wrapping)

SELECT IssueDate, Client, AgencyCode
FROM SPACEBOOKING
WHERE (IssueDate BETWEEN #05/01/2006# AND #05/31/2006#)
AND (AgencyCode = 1595)
ORDER BY IssueDate

Thanks! But it is being executed correctly from Sql Server using actual values.
Problem lies in executing it from VB 2005 code(using either actual values or variable names)

Also, I think lines of code you have given are about vb6. But I am talking about VB 2005.

[Killer42]

Thanks! But it is being executed correctly from Sql Server using actual values.
Problem lies in executing it from VB 2005 code(using either actual values or variable names)

Also, I think lines of code you have given are about vb6. But I am talking about VB 2005.

Guilty! (In fact, I told you I was writing VB6).

Anyway, let's get back to basics. What happens if you use this string...

SELECT IssueDate, Client, AgencyCode
FROM SPACEBOOKING
WHERE (IssueDate BETWEEN #05/01/2006# AND #05/31/2006#) AND (AgencyCode = 1595)
ORDER BY IssueDate

Also, can you show me exactly how it is being used? There are so many different ways to do database access these days it can be hard to pin things down.

One more note - you may get a quicker answer in either the Access or .Net forum (possibly both).