Date format in parameterised query.

[Tim Marsden]

Hello,

I am building a parameterised query in vb.net for execution against a SQL
server database.
I am using a OLEDB command and OLEDB parameters. If one of the parameters is
a date I sometimes experience a problem in the interpretation of the format.
I populate the parameter value from a user input text box. I am in the UK so
the use inputs in the format dd/mm/yy. I know SQL user the US format of
mm/dd/yy.

Is there any simple way of telling the query or the the connection or
anything, the data format I am using is dd/mm/yy.
I could convert the date to mm/dd/yy before setting the parameter value, but
the same logic is used for all data types.

Regards Tim.

[Stephany Young]

Assuming that the syntax of your query is correct and the parameter is
correctly defined as the appropriate date type then you feed it the value of
a date type. There is no need to format it however you may need to use
CDate(<textbox>.Text).

"Tim Marsden" <[e-mail]> wrote in message
news:Or**************@TK2MSFTNGP11.phx.gbl...

Hello,

I am building a parameterised query in vb.net for execution against a SQL
server database.
I am using a OLEDB command and OLEDB parameters. If one of the parameters is a date I sometimes experience a problem in the interpretation of the format. I populate the parameter value from a user input text box. I am in the UK so the use inputs in the format dd/mm/yy. I know SQL user the US format of
mm/dd/yy.

Is there any simple way of telling the query or the the connection or
anything, the data format I am using is dd/mm/yy.
I could convert the date to mm/dd/yy before setting the parameter value, but the same logic is used for all data types.

Regards Tim.

[Kevin Yu]

Thanks for Stephany's reply.

Tim,

I'd like to give more information on this. CDate() sometimes doesn't work
in different cultures. To specify the culture in parsing a date you can try
the following codes:

Dim dt As DateTime
Dim culture As IFormatProvider = New
System.Globalization.CultureInfo("en-GB")
dt = DateTime.Parse(Me.TextBox1.Text, culture)

dt will contain the DateTime value and you can assign it to a DateTime
parameter in a SQL statement.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

[Cor]

Kevin Yu,
Normaly culture is normaly arranged by the operating system.
I think Stephany's answer is basicly correct (without error handling and
so).
Your answer makes the program culture dependable.
I think that when a culture fails it is better to look for the
language-culture settings in the computer (and in 9X computers the keyboard
settings )
Cor

[Charles Rumbold]

Tim,

Agree with Stephany & Kevin.

You have a 2 stage problem:
1 - Get a valid correct date from user input text
2 - Pass date to DB.

Don't combine the 2 and pass the input string direct to the DB - bad
coding and lays you open to attack.

The first is culture specific but I don't find this reliable (an
English user on a French client talking to a server in the US??). I
prefer to keep control over date formats by using a UserProfile or
being clear. I accept this may not be an option in public
applications (as opposed to identified users). If you use a
prescribed date format use a Validator to check the format and save a
server round trip.

The second should not be an issue if you use parameters. If you don't
use parameters then use an unambigous format.

SqlCommand.Text = "SELECT ... FROM ... WHERE ( START_DATE = '" +
myDate.ToString("yyyy/MM/dd") + "')"
- but its better to use parameters

Charles

"Tim Marsden" <[e-mail]> wrote in message
news:Or**************@TK2MSFTNGP11.phx.gbl...

Hello,

I am building a parameterised query in vb.net for execution against a SQL server database.
I am using a OLEDB command and OLEDB parameters. If one of the parameters is a date I sometimes experience a problem in the interpretation of the format. I populate the parameter value from a user input text box. I am in the UK so the use inputs in the format dd/mm/yy. I know SQL user the US format of mm/dd/yy.

Is there any simple way of telling the query or the the connection or anything, the data format I am using is dd/mm/yy.
I could convert the date to mm/dd/yy before setting the parameter value, but the same logic is used for all data types.

Regards Tim.

[Kevin Yu]

Hi Cor,

Thanks for you reply. Stephany's answer is right if the culture of the
machine has already been set to English (United Kingdom). My code makes the
input fixed to the dd/mm/yy style.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."