Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Date conversion problems in VB SQL statements

During the execution of an SQL statement in VB express i get the following error

"the coversion of a varchar type to a datetime type resulted in an out of range value"

The VB code is below.  The error is triggered only when the mind or maxd variables are used in the SQL statements.  When i hover over the variables they all look to be in the correct format but obviously they are not.

Any help most welcome.

Dim sd As DateTime = datStart.Value.ToString("yyyy/MM/dd")
            Dim ed As DateTime = datEnd.Value.ToString("yyyy/MM/dd")
            Dim Result As DialogResult
            Result = MsgBox("Are you sure the dates are correct?", vbQuestion + vbYesNo, "Message Alert")
            If Result = System.Windows.Forms.DialogResult.Yes Then
                Dim mind As DateTime
                Dim maxd As DateTime
                If datStart.Format = DateTimePickerFormat.Long Then
                    'Date Range OEE
                    SQL.RunQuery("SELECT Max(Date) As MaxDate FROM qry_OEE_DateRange")
                    For Each i As Object In SQL.SQLDataSet.Tables(0).Rows
                        maxd = i.Item("MaxDate")
                    Next
                    SQL.RunQuery("SELECT Min(Date) As MinDate FROM qry_OEE_DateRange")
                    For Each i As Object In SQL.SQLDataSet.Tables(0).Rows
                        mind = i.Item("MinDate")
                    Next
                    If sd >= mind And ed <= maxd Then
                        SQL.RunQuery("SELECT OEE As OEE, Date As Date FROM qry_OEE_DateRange WHERE Date >= '" & sd & "' And Date <= '" & ed & "' ")
                    ElseIf sd >= mind And ed > maxd Then
                        SQL.RunQuery("SELECT OEE As OEE, Date As Date FROM qry_OEE_DateRange WHERE Date >= '" & sd & "' And Date <= '" & maxd & "' ")
                    ElseIf sd < mind And ed <= maxd Then
                        SQL.RunQuery("SELECT OEE As OEE, Date As Date FROM qry_OEE_DateRange WHERE Date >= '" & mind & "' And Date <= '" & ed & "' ")
                    ElseIf sd < mind And ed > maxd Then
                        SQL.RunQuery("SELECT OEE As OEE, Date As Date FROM qry_OEE_DateRange WHERE Date >= '" & mind & "' And Date <= '" & maxd & "' ")
                    End If

"the coversion of a varchar type to a datetime tyoe resulted in an out of range value"
0
SweetingA
Asked:
SweetingA
  • 4
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please read up this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html


your issue is (for example) here:
WHERE Date >= '" & mind & "'
after reading the article, you will understand that you are doing there 2 implicit data type conversions, both uncontrollable with this code

much better (and simpler in regards of the code) would be parametrized queries
0
 
SweetingAAuthor Commented:
Thanks Guy but your talking to an abolute rookie and even after reading the above i have little clue how to proceed here - can i have another clue please.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't have vb.net machine on my side, but to start with, let's simplify/optimize some code:
SQL.RunQuery("SELECT Max(Date) As MaxDate FROM qry_OEE_DateRange")
                    For Each i As Object In SQL.SQLDataSet.Tables(0).Rows
                        maxd = i.Item("MaxDate")
                    Next
                    SQL.RunQuery("SELECT Min(Date) As MinDate FROM qry_OEE_DateRange")
                    For Each i As Object In SQL.SQLDataSet.Tables(0).Rows
                        mind = i.Item("MinDate")
                    Next

Open in new window

should become:
SQL.RunQuery("SELECT Max(Date) As MaxDate, Min(Date) As MinDate  FROM qry_OEE_DateRange")
                    For Each i As Object In SQL.SQLDataSet.Tables(0).Rows
                        maxd = i.Item("MaxDate")
                        mind = i.Item("MinDate")
                    Next

Open in new window


then, instead of building 4 potential sql statments, build 1:

  Dim pmind As DateTime
  Dim pmaxd As DateTime

  pmind = sd
  pmaxd = ed

  if ed > maxd Then  pmaxd = maxd
  if sd < mind Then  pmind = mind
  
  SQL.RunQuery("SELECT OEE As OEE, Date As Date FROM qry_OEE_DateRange WHERE Date >= '" & pmind & "' And Date <= '" & pmaxd & "' ")

Open in new window

this does not yet solve your problem, but it's just writing better (and much less code)
of course, then the question is why you actually query for mind and maxd, because if the user specifies a sd < mind, the query with sd will work correctly

in regards to your actual problem, there are 2 approaches: a ad-hoc sql solution, and the above-mentioned parametrized query method.
the first one could look like this:
  SQL.RunQuery("SELECT OEE As OEE, Date As Date FROM qry_OEE_DateRange WHERE Date >= '" & pmind.ToString("YYYYMMDD") & "' And Date <= '" & pmaxd.ToString("YYYYMMDD") & "' ")

Open in new window


for the second one, I would need to know what the "SQL" variable data type/class is exactly.
you surely can find some tutorials around when searching for that data type class and "parametrized query"
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
SweetingAAuthor Commented:
when i don't use maxd and mind i always get an error stating i am out of range
0
 
SweetingAAuthor Commented:
The SQL statement above results in teh following error.

"conversion failed when converting character to a string"

to create a date in the format i wanted in SQL i used CAST(FLOOR(CAST(Date AS float)) AS datetime), the original date type was datetime

thanks for the help so far
0
 
SweetingAAuthor Commented:
Hi Guy,

Just a very small change to the format required to make it work - thanks - i will award the points now.

SQL.RunQuery("SELECT OEE As OEE, Date As Date FROM qry_OEE_DateRange WHERE Date >= '" & pmind.ToString("yyyy/MM/dd") & "' And Date <= '" & pmaxd.ToString("yyyy/MM/dd") & "' ")
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now