How to pass date to SQL serever from VB 1

[asimasm]

Hi
Plz tell me what is the correct way to pass date values from Visual Basic DtpPicker to SQL server. Bcz both Both dtpPicker(VB) and SQL server are locale aware therefore the values sometimes get wrong i.e month is saved in day and vise versa. For example i am have selected US Month/day/year settings at my PC and my SQL server has German(swiss) date.month.Year format so how will i pass these values. Also i have to consider Wt will happen when any of the server or the client changes his regional setttings.

 

[rajeevnandanmishra]

Hi asimasam,
You don't have to think about the format date stored in SQL server.
You are the developer you just have to be confirm that in which format user is going to enter/retreive the date.
Set your format in the starting of your application by passing the following SQL to server:
SET DATEFORMAT DMY (dd/mm/yyyy)
or
SET DATEFORMAT MDY (mm/dd/yyyy)
or
SET DATEFORMAT YMD (yyyy/mm/dd)

Once you hade done this, SQL will maintain a reference of dateformat for that session.
Now when passing the date to SQL, just be sure that your format is the same what you had setted by issuing dateformat SQL. (you can pick month, day and year property from the dtpicker instead of value property, and then convert it to your required date format.)

This will allow you to program without thinking about the SQL Date settings or Client date settings.

Just go through thread183-99681 also, if you have time.

Hope it will move you in right direction.