Date format in parameterised query.

  • Thread starter Thread starter Tim Marsden
  • Start date Start date
T

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.
 
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).
 
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."
 
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
 
Back
Top