Results 1 to 13 of 13

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

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    23

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

    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

  2. #2
    PowerPoster JuggaloBrotha's Avatar
    Join Date
    Sep 2005
    Location
    Lansing, MI; USA
    Posts
    4,286

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

    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).
    Currently using VS 2015 Enterprise on Win10 Enterprise x64.

    CodeBank: All ThreadsColors ComboBoxFading & Gradient FormMoveItemListBox/MoveItemListViewMultilineListBoxMenuButtonToolStripCheckBoxStart with Windows

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

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

    Quote Originally Posted by JuggaloBrotha View Post
    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.

  4. #4
    PowerPoster JuggaloBrotha's Avatar
    Join Date
    Sep 2005
    Location
    Lansing, MI; USA
    Posts
    4,286

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

    Quote Originally Posted by dilettante View Post
    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.
    Currently using VS 2015 Enterprise on Win10 Enterprise x64.

    CodeBank: All ThreadsColors ComboBoxFading & Gradient FormMoveItemListBox/MoveItemListViewMultilineListBoxMenuButtonToolStripCheckBoxStart with Windows

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

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

    Quote Originally Posted by JuggaloBrotha View Post
    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.
    Last edited by dilettante; Feb 2nd, 2013 at 12:40 AM.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    23

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

    Quote Originally Posted by imrankhaldi View Post
    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

  7. #7
    PowerPoster JuggaloBrotha's Avatar
    Join Date
    Sep 2005
    Location
    Lansing, MI; USA
    Posts
    4,286

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

    Quote Originally Posted by imrankhaldi View Post
    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:
    vb Code:
    1. Dim SqlStr As String
    2. 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.
    Currently using VS 2015 Enterprise on Win10 Enterprise x64.

    CodeBank: All ThreadsColors ComboBoxFading & Gradient FormMoveItemListBox/MoveItemListViewMultilineListBoxMenuButtonToolStripCheckBoxStart with Windows

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Jun 2012
    Posts
    23

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

    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.

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

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

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

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

    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.
    Last edited by FunkyDexter; Feb 5th, 2013 at 10:29 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

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

    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.

  12. #12
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

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

    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.
    Last edited by FunkyDexter; Feb 5th, 2013 at 11:35 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  13. #13
    PowerPoster JuggaloBrotha's Avatar
    Join Date
    Sep 2005
    Location
    Lansing, MI; USA
    Posts
    4,286

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

    Quote Originally Posted by imrankhaldi View Post
    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.
    Last edited by JuggaloBrotha; Feb 11th, 2013 at 11:03 AM.
    Currently using VS 2015 Enterprise on Win10 Enterprise x64.

    CodeBank: All ThreadsColors ComboBoxFading & Gradient FormMoveItemListBox/MoveItemListViewMultilineListBoxMenuButtonToolStripCheckBoxStart with Windows

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width