[RESOLVED] Vb-Sql 2000 Date format Problem in query

[BlueFox]

I am using vb6 for developing my database application for storing and displaying data. The Problem is the date format in different computers using my application. The Client has different computers where date format differ like 'mm/dd/yyyy' and 'dd/mm/yyyy'. When inserting and displaying data, the server takes date as its regional settings which is different from node's. That is when node sends '09/10/2013' (the 09 is day) the server stores it as sep- 10 -2013. Also when displaying data the server misinterprets the node's request and sends wrong data.I cannot change their date formats using my application. (Its Their Settings)
Is there any way to send datetime as double and decode it in server? Any Fix? Thank You.
I searched many threads but did not find answer suitable for me. I don't know if this is the right forum to post. But I am using vb6.

[ryanframes]

use format 'yyyy-mm-dd' when updating the database.

[techgnome]

Depends... how are you sending the dates to the database? PArameters or are you concatenatiing strings together? If you use parameters, it should make the problem go away. should.

-tg

[BlueFox]

When Updating I Try To Use Procedures. But When displaying Reports, the query is string and I am using recordset. Are you saying that I don't have to worry when I am using parameters?

[ryanframes]

so when updating , is the date format still in the client's regional setting ?
also, what component that you use to display the reports ?

[szlamany]

Are you saying that I don't have to worry when I am using parameters?

As soon as you turn your "data value" into a STRING you lose control over the fact that it was a date.

Parameters can accept DATETIME datatypes - so the "data value" never goes into the "evil" string world.

If you are going to make an UPDATE and use a non-parameter method - thus a STRING is involved - you best make it YYYY-MM-DD at that moment so you can avoid regional setting problems.

[BlueFox]

I don't think, while using the parameters, I ever got wrong date. The problem was while building query for report, I embed the date into the query string from the date picker. Some records are missing. Like november 10 becomes october 11 in select query. Its not the problem of component. Its that I don't get all the data into the recordset from database while using between etc.

[ryanframes]

can you show us your query ?

[techgnome]

I don't think, while using the parameters, I ever got wrong date. The problem was while building query for report, I embed the date into the query string from the date picker. Some records are missing. Like november 10 becomes october 11 in select query. Its not the problem of component. Its that I don't get all the data into the recordset from database while using between etc.

because you're converting it into a string for your query... that's what we're trying to tell you... if you used parameters in the query, it shouldn't be a problem... it sounds like you're not... and as a result, some dates are being misinterpreted (likely your passed in dates to the where clause).

-tg

[BlueFox]

While Clicking Search Button On Server This is the query being generated:
select * from admission where deleted=0 and dob between '12/09/2013' and '12/11/2013'
But the same search button generates this on node:
select * from admission where deleted=0 and dob between '09/12/2013' and '11/12/2013'
Therefore Results Differ.
I am going to Change It To Procedure/'Parameterized' Queries. Th.Yu.

Here is the code I am going to use
Dim sqlCmd As ADODB.Command
Set sqlCmd = New ADODB.Command
sqlCmd.ActiveConnection = Ycnn
sqlCmd.CommandType = adCmdStoredProc
sqlCmd.CommandText = "proDisplayHolidays"
sqlCmd.Parameters("@holidayfrom") = dtpFrom.value
sqlCmd.Parameters("@holidayto") = dtpTo.value

sqlcmd.execute 'Catch the results in recordset

Will this be enough?

[ryanframes]

sqlCmd.Parameters("@holidayfrom") = dtpFrom.value
sqlCmd.Parameters("@holidayto") = dtpTo.value

assuming that on your stored procedure the parameters data type's datetime, so when store the parameters use format 'yyyy-mm-dd' like this :

sqlCmd.Parameters("@holidayfrom") = Format(dtpFrom.value,"yyyy-mm-dd")
sqlCmd.Parameters("@holidayto") = Format(dtpTo.value,"yyyy-mm-dd")

[dilettante]

Er, now we're back to formatting dates as String values and relying on implicit coercion again.

[BlueFox]

Er, now we're back to formatting dates as String values and relying on implicit coercion again.

So Should I omit Format Function?

[techgnome]

You should be using parameters properly and not formatting a thing...

-tg

[szlamany]

As soon as you turn your "data value" into a STRING you lose control over the fact that it was a date.

Parameters can accept DATETIME datatypes - so the "data value" never goes into the "evil" string world.

If you are going to make an UPDATE and use a non-parameter method - thus a STRING is involved - you best make it YYYY-MM-DD at that moment so you can avoid regional setting problems.

I am guessing this post did not make sense?

[BlueFox]

Parameters Then!!.