• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 144
  • Last Modified:

Date switching works weird from 1 laptop .. causing out of range...

dim dData as date
dim strSQL as string
dDate = '01/16/2005'

I am using Visual Basic .NET to develop an application. Temporarily, my machine is acting as a server. But, only 1 machine  from the LAN tried to enter data, it's

working strangely. I tried to do remote debugging and these are the things i noticed... i simplied the code as below...

strsql = "SELECT * from tblA where StopDate = '" & dDate & "' "

All the machine  will have strsql = SELECT  * from tblA where StopDate = '01/16/2005' .
But, 1 machine SQL statement appears as   strsql = SELECT  * from tblA where StopDate = '16/01/2005'  which causes OUT of range date (month becomes 16). Why????Why??? Why when i try to concatenate, it is automatically switching the date again.
I already checked the Regional settings and set the date to  M/d/yyyy  , MM/dd/yyyy and also yyyy-MM-dd. But, nothing seems to help the 1 machine.

TQ
Any suggestions greatly appreciated.
0
pandav
Asked:
pandav
  • 2
1 Solution
 
Arthur_WoodCommented:
the machine where you get this error, probably has its REGIONAL settings set to format dates as DD/MM/YYYY (European format), which the ohters are set for MM/DD/YYYY (American dates).


How are you getting the variable dDate?

you might try adding code, like this:

If IsDate(dDate) then
     strsql = "SELECT * from tblA where StopDate = '" & dDate & "' "
Else
     strsql = "SELECT * from tblA where StopDate = '" & FixDate(dDate) & "' "
End If

Private Function FixDate(strDate as String) as String
   Dim lDate() as String
   lDate = strDate.Split("/")
   Return lDate(1) & "/" & lDate(0) & "/" % lDate(2)
End Function

this function will swap the first and second values in the 'date string' (having the effect of converting 16/01/2005 to 01/16/2005

AW


0
 
Arthur_WoodCommented:
or try this:

If IsDate(dDate) then
     strsql = "SELECT * from tblA where StopDate = '" & dDate & "' "
Else
     strsql = "SELECT * from tblA where StopDate = '" & FixDate(dDate) & "' "
End If

Private Function FixDate(strDate as String) as Date
   Dim lDate() as String
   lDate = strDate.Split("/")
   Return DateSerial(cInt(lDate(2)), cInt(lDate(1)), cInt(lDate(0)))
End Function

AW
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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