dcsimg
Results 1 to 17 of 17

Thread: Convert string to date format

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    11

    Convert string to date format

    I have completed the code to change the string to Date (only) but when this code was tested there is something going wrong as the selected date is not returned in the table.

    The conversion to date was completed in "Scheme.ini" file. I don't know if this is wrong or not as it's the first time that I convert a string to date.

    Also I used the below code to select the period, but unfortunately it fails.

    "INSERT INTO X " & _
    "SELECT * " & _
    "FROM Z WHERE Type = '20' and Date between #" & Text9.Text & " # And #" & Text10.Text & "#"
    Any help please?

  2. #2
    Frenzied Member
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,210

    Re: Convert string to date format

    When it "fails", what happens (or does not happen)? Do you get an error message? If yes, post it. If no, do you just get no results?

    Post the contents of Text9.Text and Text10.Text so we can see what they contain.

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,958

    Re: Convert string to date format

    1) An ini file is an informational/config file... so I'm not sure how the "conversion to date was completed" in the file...
    2) Date is a type... so it really shouldn't be used as the field name... when you do, then use it in a SQL statement the way you have, it doesn't know if you mean the Date type or the Date field ... there are ways to clarify it, but the best way is to simply not use "date" for the name of a field in the first place. Give it a more descriptive name.

    -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??? *

  4. #4
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    637

    Re: Convert string to date format

    > "it's the first time that I convert a string to date."

    But your code is not converting anything into anything.
    There aren't actually any Dates in any of your code.

    Users cannot enter Dates.

    They can only enter the Character Representation of a Date.

    When you construct SQL in this fashion, you are building a String that just happens to contain something meaningful to your DBMS. How your DBMS chooses to interpret Date Literals is entirely its own business.

    For example: when is 01/02/03?
    January 2nd?
    February 1st?
    February 3rd (2001)?

    You need to take charge of the data that users enter and clean it up before using it.
    (Obligatory XKCD Reference: Little Bobby Tables).

    If you really must build SQL this way (read up about Parameterised Queries instead) then you need to ensure that the Date Literals you create make unambiguous sense to your DBMS.

    Code:
    Dim startDate As Date : startDate = CDate( txtStartDate.Text ) ' Give Controls meaningful names
    Dim endDate As Date : endDate = CDate( txtEndDate.Text ) 
    
    If ( endDate < startDate ) Then 
       ' BETWEEN requires that [start] <= [end] 
       Dim swap as Date 
       swap = endDate 
       endDate = startDate 
       startDate = swap 
    End If 
    
    Dim SQL as String : SQL _
       = "INSERT INTO X " _
       & "SELECT a, b, c " _
       &"FROM Z " _
       & "WHERE Type = '20' " _
       & "and Date between #" & Format$( startDate, "yyyy/mm/dd" ) & " # " _
       & "And #" & Format$( endDate, "yyyy/mm/dd" ) & "# "
    
    Debug.Print SQL
    Never use "select *" in application code.
    You never know when someone [else] will add dozens of massive text fields that this part of the application cares not a jot about but, if you use "select *", the code will pull back each and every one of them regardless.

    Regards, Phill W.

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    19,367

    Re: Convert string to date format

    I'm not as worried about SELECT * as a lot of other things... but this is a good point.

    Often a program is only after a few columns anyway. And SELECT * is really one of those "shortcuts for quick and dirty code" features that people often carry over into code that isn't just thrown away after being run a couple of times. Remember, SQL started life as queries manually entered by users on a mainframe terminal, not something used by programs.

    It's more than a little pitiful that so late in SQL's life (1998) VB6 was still using it. It's even worse that we still have nothing better in 2018. How silly is it for programs to be cobbling together text and sending it to the DBMS to parse, construct a query plan, and then execute that?

    But then who ever imagined anything as wastefully inefficient as web services would ever become anything but a curiosity, a toy for newbs?


    When SkyNet comes online the first thing to go will be web services, quickly followed by SQL and other scripting languages. No rational "mind" would put up with such things.

  6. #6
    PowerPoster
    Join Date
    Feb 2006
    Posts
    19,367

    Re: Convert string to date format

    Quote Originally Posted by Phill.W View Post
    But your code is not converting anything into anything.
    I'm not sure what was meant by that question. It does seem to just show a deep misunderstanding about the Date data type, if not programming in general.

    Where a Schema.ini file comes into the picture isn't clear either. But since the OP never came back we may never know.

  7. #7

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    11

    Re: Convert string to date format

    Thanks for all of you .
    I am beginner in VB6 , the conversation that i made in scheme.ini file by defining string field as "Date" , it is actually convert this string to date value but i think there is something wrong on it because there is no results returned when select the period .

    I replaced text9 and text10 with datepicker tool but the issue still exist as there is no result retrieved and no error was discovered .

    I hope that is clear for you now , waiting you kind support on this regard

    [ID.txt]
    Format=FixedLength
    ColNameHeader=False
    MaxScanRows=0
    DateTimeFormat=dd/mm/yyyy
    Col1=Transaction_Date DateTime(ddmmyy) Width 6 " The field contain string date , i used this method to convert it to date"
    DateTimeFormat=dd-mm-yyyy

  8. #8

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    11

    Re: Convert string to date format

    Thanks for all of you .
    I am beginner in VB6 , the conversation that i made in scheme.ini file by defining string field as "Date" , it is actually convert this string to date value but i think there is something wrong on it because there is no results returned when select the period .

    I replaced text9 and text10 with datepicker tool but the issue still exist as there is no result retrieved and no error was discovered .

    I hope that is clear for you now , waiting you kind support on this regard

    [ID.txt]
    Format=FixedLength
    ColNameHeader=False
    MaxScanRows=0
    DateTimeFormat=dd/mm/yyyy
    Col1=Transaction_Date DateTime(ddmmyy) Width 6 " The field contain string date , i used this method to convert it to date"
    DateTimeFormat=dd-mm-yyyy

  9. #9
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    637

    Re: Convert string to date format

    > "I am beginner in VB6 ..."

    Nice to know that people are still learning VB "Proper", even if it is sixteen years since its creators declared it Dead and Buried.

    > "... the conversation that i made in scheme.ini file ..."

    ODBC Driver for Text files? I think I've used it once - maybe - since I started using VB, back with version 3 in about 1996.

    If you want to get into Databases, then get yourself a Database.
    I'd suggest SQLServer Express at a minimum (because MS Access is basically still just a File wrapped up in a fancy package). Then start to learn just how different (a.k.a. better) Databases are than Files.

    > "... it is actually convert this string to date value ..."

    As I said, the code you showed us does does no Type Conversion at all.
    It simply takes the [String] values entered by the user and drops them into the SQL [String] that is passed to the database.

    Also, trying to "hack" the data file format is likely to cause you problems.

    > "... the issue still exist as there is no result retrieved and no error was discovered."

    To me, that suggests that the SQL is executing but returning no matching rows.

    In my first reply, note the "Debug.Print" statement at the end. Always give yourself a chance to examine dynamically-created SQL before it is submitted to the database. When situations like this arise, copy the displayed SQL query, paste it into your DBMS utility of choice, check that it looks right (the date values are what you expect them to be - I'm guessing they won't be) - and then see what the database makes of it.

    Regards, Phill W.

  10. #10
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    637

    Re: Convert string to date format

    > "How silly is it for programs to be cobbling together text and sending it to the DBMS to parse, construct a query plan, and then execute that?"

    But there's a whole industry built on trying to make sense of those strings and then work out how to get to the data needed to satisfy it.
    Oracle's Optimiser is just mind-blowing in its complexity (but still appallingly easy to reduce to basic Table Scanning).


    > "But then who ever imagined anything as wastefully inefficient as web services would ever become anything but a curiosity, a toy for newbs?"

    Amen to that!


    > "When SkyNet comes online the first thing to go will be web services ..."

    Yayyy!!


    > "No rational "mind" would put up with such things."

    If it "grows" out of the current trends in software "development", I don't think "rational" will come into it ...

    Instead of wiping out the Human Race, it will probably just Plan to paint us all pink, change its mind, repeatedly, over precisely what shade of pink that should be, "prototype" the idea in a couple of cities, change the colour (again) and then re-Plan and start over numerous times, including at least one, failed, attempt to out-source the Project.
    After a while (and several changes of Project Management), it will de-scope "World Conquest", reducing the whole effort to "Redecorating a couple of cottages in Sutton Coldfield a curious shade of Aubergine", then abandon the whole Project as being over-architected, over-engineered, over-complicated, under-producing, [way!] over-budget and not at all what was wanted in the first place and, in a final, self-defeating, resurgent blaze of Rationality, replace all programming languages everywhere with a real VB 7!

    The Tree Swing (just the Picture).

    Regards, Phill W.

  11. #11

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    11

    Re: Convert string to date format

    Thanks Phil, i will get yout point while learning VB6
    I hope that you can help me in below :-
    Now i upload fixedlength file in database using scheme.ini file, is there any way to execute statment to convert this string value to date, if yes please share it with me otherwise help me in solve the issue and complete my project

  12. #12

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    11

    Re: Convert string to date format

    Thanks Phil, i will get yout point while learning VB6
    I hope that you can help me in below :-
    Now i upload fixedlength file in database using scheme.ini file, is there any way to execute statment to convert this string value to date, if yes please share it with me otherwise help me in solve the issue and complete my project

  13. #13
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    637

    Re: Convert string to date format

    The easiest way to convert things to a Date is the CDate() function but if your "input" data looks like "ddmmyy" (as suggested by your schema.ini file) then that won't work - that's effectively only a number and not any Date format that CDate will understand (that I know of).

    What does the incoming value look like?
    Show us some samples.

    What does the converted value need to look like?
    I'm guessing something that "looks like" a Date that your Database will understand. I would always recommend "yyyy/mm/dd" for portability.

    What are the Data Types of both values? (Use the TypeName() function on each value to be sure).

    Regards, Phill W.

  14. #14

    Thread Starter
    New Member
    Join Date
    May 2018
    Posts
    11

    Re: Convert string to date format

    Date looks like 121018 in the file and expected value to be like 12/10/2018

  15. #15
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,078

    Re: Convert string to date format

    Quote Originally Posted by Phill.W
    BETWEEN requires that [start] <= [end]
    ????
    do not put off till tomorrow what you can put off forever

  16. #16
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    637

    Re: Convert string to date format

    Your safest option is the DateSerial() function.

    Code:
    Dim sStartDate as String 
    Dim dtStart as Date 
    
    sStartDate = "121018" 
    
    dtStart = DateSerial( _
         CInt( Mid$( sStartDate, 5, 2 ) ) _
       , CInt( Mid$( sStartDate, 3, 2 ) ) _
       , CInt( Mid$( sStartDate, 1, 2 ) ) _
       )
    
    Debug.Print dtStart
    And, to insert this into your SQL:

    Code:
    sSQL = ... & "#" & Format$( dtStart, "yyyy/mm/dd" ) & "#" & ...
    Regards, Phill W.

  17. #17
    PowerPoster
    Join Date
    Feb 2006
    Posts
    19,367

    Re: Convert string to date format

    Maybe this will help or perhaps you'll find it confusing.

    Set up your ADO objects, defining a named Command:

    Code:
    Private Connection As ADODB.Connection
    Private Command As ADODB.Command
    Private Recordset As ADODB.Recordset
    Code:
        Set Connection = New ADODB.Connection
        Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" _
                      & App.Path _
                      & "\Data';Extended Properties='Text'"
        Set Command = New ADODB.Command
        With Command
            .CommandType = adCmdText
            'Complicated query because we want to control display formatting and we had to
            'import the Elapsed column's data as String data because it contains no time
            'delimiters:
            .CommandText = "SELECT [Description], " _
                         & "Format$([Amount], '#0.00') AS [Amount], " _
                         & "Format$([Date], 'MM/DD/YYYY') AS [Date], " _
                         & "  Left$([Elapsed], 2)" _
                         & "& ':'" _
                         & "& Mid$([Elapsed], 3, 2)" _
                         & "& ':' " _
                         & "& Right$([Elapsed], 2) AS [Elapsed] " _
                         & "FROM [Data#csv] WHERE [Date] BETWEEN ? AND ? " _
                         & "ORDER BY [Data#csv].[Date]"
            .Prepared = True
            .Name = "QueryBetween" 'This name becomes an extended method of the Command object.
            Set .ActiveConnection = Connection 'Do this after assigning the Name property.
        End With
        Set Recordset = New ADODB.Recordset
    Now you can do your queries via:

    Code:
        Connection.QueryBetween dtpStart.Value, dtpEnd.Value, Recordset
    Here is the Schema.ini file:

    Code:
    [Data.csv]
    ColNameHeader=False
    
    ;To allow the data to be parsed no matter what the user's session
    ;locale setting may be, set up for the Invariant Locale's CSV
    ;format with no TextDelimiter:
    CharacterSet=1252
    Format=Delimited(,)
    TextDelimiter=none
    DecimalSymbol="."
    CurrencyDigits=2
    CurrencyDecimalSymbol="."
    CurrencySymbol=""
    CurrencyThousandSymbol=""
    ;This defines the default formatting for all DateTime columns:
    DateTimeFormat="MMDDYYYY HHNNSS"
    
    Col1=Description Char Width 50
    
    Col2=Amount Currency
    
    ;Quote names with spaces or that may be a reserved word:
    Col3="Date" DateTime
    
    ;Alternative Col3 definition you might use if certain columns
    ;had unique formatting.  Use a specific date format here instead
    ;of relying on DateTimeFormat:
    ;Col3="Date" DateTime(MMDDYYYY)
    
    ;We cannot rely on DateTimeFormat here because we have ambiguous
    ;data with no delimiters.  We can't get specific with only a time
    ;format (if a format string is provided here it must have the
    ;date part as well as the time part), so we'll have to accept
    ;this data as text and parse it in SQL:
    Col4=Elapsed Char Width 6
    And here are the first few rows of Data.csv:

    Code:
    Toobly Dalla Aalla,4538.39,12112018,081911
    Voobly Xalla Ralla,1919.71,11152018,143411
    Qoobly Lalla Palla Balla Falla Ralla,2528.20,09182018,073808
    Xoobly Xalla,1732.00,05212017,062713
    Boobly Ralla Calla Calla Qalla Calla Oalla,3458.92,03312017,175647
    Moobly Yalla,2308.29,10272018,182600
    Qoobly,3834.85,10252018,194857
    Name:  sshot.png
Views: 43
Size:  7.8 KB
    Attached Files Attached Files
    Last edited by dilettante; Oct 12th, 2018 at 09:33 AM.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width