how to enter date in british format in sql server through vb6 application

[imrankhaldi]

i am confronting a problem while entering data into sql database through vb application. infact when user enter data including date through vb application form, it does not create any problem if the date is entered in mm/dd/yyyy but if he enter date in british format like dd/mm/yyyy then it creates error as " conversion of varchar data into dateformat resulted in out range value" please help

[JuggaloBrotha]

You don't, you let the DB store the date in the localized format (which is almost always most likely to be the US standard mm/dd/yyyy format) and you have your application format the date when displaying it on the screen (when you cast it to a string, is when you format it to European standard dd/mm/yyyy).

[dilettante]

You don't, you let the DB store the date in the localized format (which is almost always most likely to be the US standard mm/dd/yyyy format) and you have your application format the date when displaying it on the screen (when you cast it to a string, is when you format it to European standard dd/mm/yyyy).

Almost.

And stop saying "cast." This is conversion. Repeating a term you heard somwehere because it "sounds whizzy" doesn't fool anyone. Look it up.


You'd normally use a date type that is native to the DMBS in use. You'd avoid storing a string. So for Jet, SQL Server, etc. you'd pass a Date type value to your data connector (ADO, ADO.Net, etc.) just like the values your program itself should be operating on (not strings).

Some DBMSs support other date types and require special handling. And a few are so primitive everything is a string to them, so for these you need to create your own universal format and deal with converting to and from that.


You really want to avoid making users hand-enter dates. Use a date-picker control whenever possible. Otherwise it is up to you to determine the current locale and do the conversion yourself.

Similar issues apply for outputting dates unless you use the system-standard formats that automatically handle locale differences.


It sounds like your program is slapping a String into the face of your connector object (ADO Field, etc.) and forcing it to atempt conversion. Bad idea. Your program should do the conversion inteligently and then give the resulting Date value to the database. Or once again, better yet use a date picker.

[JuggaloBrotha]

Almost.

And stop saying "cast." This is conversion. Repeating a term you heard somwehere because it "sounds whizzy" doesn't fool anyone. Look it up.

Wake up on the wrong side of the bed this morning?

When you "convert" an object from one type to another, that's called type casting, in the case of my post I was referring to casting a DateTime object to a String object. If it's not considered casting as you claim, how come .Net has a DirectCast to "convert" from one data type to another? There's the VB CTyper (CastType) function in the .Net framework too, nothing "whizzy" sounding about that either.

[dilettante]

When you "convert" an object from one type to another, that's called type casting...

No, it isn't.

To "cast" means to use a bit of data of one type as if it is actually another type. There is no copying involved, let alone conversion.

CType Function (convert type)

Returns the result of explicitly converting an expression to a specified data type, object, structure, class, or interface.

Saying things incorrectly misleads others into the same misunderstanding.

[imrankhaldi]

i am confronting a problem while entering data into sql database through vb application. infact when user enter data including date through vb application form, it does not create any problem if the date is entered in mm/dd/yyyy but if he enter date in british format like dd/mm/yyyy then it creates error as " conversion of varchar data into dateformat resulted in out range value" please help

let me explain again.
i am using vb6 and sql server 2000. the query is like
insert into employee values ('John','id001','text1.text')
here text1.text is a textbox on the vb form, user types here and query pick the value from the text box and send into sql table.
problem arise when user enter date in british format like dd/mm/yyyy. please help

[JuggaloBrotha]

let me explain again.
i am using vb6 and sql server 2000. the query is like
insert into employee values ('John','id001','text1.text')
here text1.text is a textbox on the vb form, user types here and query pick the value from the text box and send into sql table.
problem arise when user enter date in british format like dd/mm/yyyy. please help

We both understand what you're trying to do and what we're both saying is that it's recommended that you insert the date into the database as a date (don't worry about how it's formatted) and vb6 does have a DateTimePicker Control that you can use to have the user enter the date (it's been a long time since I've done any vb6, but I'm pretty sure it's always let you specify the date format displayed in the control), then when you insert it into the database you use it's Value property. Here's an example:

Dim SqlStr As String
SqlStr = "Insert Into [TableName] (DateFieldName) Values (#" & CStr(DatePicker1.Value.DateValue) & "#);"

Then when you pull it out of the database you can either set the DateTimePicker's Value property to the contents of the database or if you want to display the date in something like a Label or TextBox just use the Format(<variable>, "dd/MM/yyyy") function to format the date into day/month/year.

[imrankhaldi]

Thanks for your help but i am unable to overcome the problem. Infact there is no function like datepicter1 in vb6.
Is there any function which can change the british date into american or american date into British in VB6. For example If text1.text is 02/05/13, is there any way to show it as 05/02/13. can we display british date through msgbox.

[techgnome]

It's not a function it is A CONTROL ... just like a textbox or a panel or a label... there is one that's called DateTimePicker... http://msdn.microsoft.com/en-us/libr...(v=vs.60).aspx

It allows you to allow the user to enter the date in (just about) any format... then, as long as you use a Date typed variable and the .Value property of the DTP, then you should be fine.

And while this *should* work...
SqlStr = "Insert Into [TableName] (DateFieldName) Values (#" & CStr(DatePicker1.Value.DateValue) & "#);"

I don't remember if SQL Server allows the use of # for dates... but you should be using parameters anyways, with everything typed correctly...

-tg

[FunkyDexter]

At the risk of wading into an already somewhat pointless debate (as if I could ever resist that urge), the semantic difference between Cast and Convert is largely irrellavent. We all knew what JB meant and that's enough for me (and I suspect the OP as well). What's important is the the functional difference in the platform the OP is using... and in SQL Server there really isn't one. In fact SQL Server implements a Cast as a Convert. The only real difference is that Cast is Ansi standard so will work on any Ansi compliant DBMS but Convert is a little more flexible because it's SQL Server specific so can deal with SQL Server specific types. Here's a very succint link.

@Imrankhaldi - everyone's telling you to use a date time picker and with very good reason. Your user has typed 05/02/13 into a text box and you're assuming they meant that as the 5th Feb 2013. But the could have meant 2nd May 2013 (if they were an American). Or they could have 13 Feb 2005 (which would be the ansi standard interpretation of that string... sort of). If you allow them to enter the date using a textbox you simply cannot know what they mean by it. If you get them to use a date time picker instead then you KNOW what they meant because the data format is explicit. So you really should be putting a date time picker on your form instead of a text box because otherwise you are pretty much guaranteed to get bad data.

Further, if you use sql parameters as TG suggests then your application will pass them to the database as actual dates rather than string representations. Si The Geek gives a great explanation of how to handle dates correctly in this tutorial which explains the issues that surround date handling and also gives a verty good example of how to use ADO parameters properly.

[dilettante]

Sheesh. A cast is an entirely different thing from a conversion operation. They are not the same thing and no amount of wishing will make it so.

Talking wrong is thinking wrong, which leads to doing wrong.

If some SQL syntax permits a "Cast" verb that actually converts... well that's a pretty sad thing and probly shows how far incorrect thinking can go.

[FunkyDexter]

The actual meaning of Cast and Convert aren't laid down in any canonical fashion and you'll find loads of debate as to exactly what they mean. Here's a good thread from Stack Overflow that demonstrates just how much debate there is out there. It largely depends on the technical context you're coming from. That's why I said the semantic difference is largely irrelevant in the context of this thread. The only thing that matters is the difference it will make to the OP which I would suggest, in this case, is not one jot.

[JuggaloBrotha]

Thanks for your help but i am unable to overcome the problem. Infact there is no function like datepicter1 in vb6.
Is there any function which can change the british date into american or american date into British in VB6. For example If text1.text is 02/05/13, is there any way to show it as 05/02/13. can we display british date through msgbox.

I don't believe vb6 has a DateTimePicker referenced out-of-the-box so you will need to reference a library that has one, but you certainly shouldn't use a TextBox as the user will most certainly input wrong data (the date in the wrong format, a completely invalid date, etc) and that can all be avoided by using the correct control for the correct type of data.

That being said, you should also be using parameterized queries, especially for inserts, updates, & deletes; though it is good practice to use them with select queries too. FunkyDexter has posted a link to a good tutorial on how to do just that.