Results 1 to 8 of 8

Thread: Conversion Help

  1. #1
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1

    Unanswered: Conversion Help

    Hi

    Due to system restrictions I have been given a view to a pass through query, with a pre set range of data, the issue I am having is the invoice date is coming out as 20080131, I understand that this equates to (yyyymmdd), but could someone explain to me how I convert 20080131, to a recognisable date format, for I need to be able to drive sub queries from a form, where the end user enters a date range in Short date format (dd/mm/yyyy)

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Pass-through queries

    Quote Originally Posted by MarkWhyte
    Hi

    Due to system restrictions I have been given a view to a pass through query, with a pre set range of data, the issue I am having is the invoice date is coming out as 20080131, I understand that this equates to (yyyymmdd), but could someone explain to me how I convert 20080131, to a recognisable date format, for I need to be able to drive sub queries from a form, where the end user enters a date range in Short date format (dd/mm/yyyy)

    Thanks
    Simplest suggestion would be to ask nicely for the pass-through query to be changed to provide the data in the format you require - DD/MM/YYYY.

    Let me know if this is not possible.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    imediate window transcript:
    ? isdate(20080325)
    False
    ? isdate("20080325")
    False
    ? isdate(2008-03-25)
    False
    ? isdate("2008-03-25")
    True


    so. you have a viable date candidate but it needs a couple of "-" to be understood.
    myString = left$(somestring, 4) & "-" & mid$(somestring, 5, 2) & "-" & mid$(somestring, 7,2)

    but better is to have the provider provide reasonable data as Gareth suggests.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb DateTime

    To be more specific, ask them to *convert to a DateTime datatype - suspect this will make it easier for you to run queries against it based on user input.

    *Given the output you have shown, im guessing the SQL datatype is maybe nvarchar, text or similer?

    Let me know if you need more help.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im sure Izyrider is right (he neqrly always is, actually if the truth be told....)

    personally id coerce the the string into a date varaible and stroe it as such

    eg cdate(mystringvariable)

    id build mystringvariable using the string manipulation functions left$ & mid$
    eg
    say you had a varaible called ProdDate set to "20080131"

    mystringvariable = left$(ProdDate,4) & "/" & mid$(proddate,5,2) & "/" & mid$(proddate,7,2)
    which will make mystringvariable = 2008/01/31
    to get that into a date varaible
    mydatevaraiable=cdate(mystringvariable)
    you could roll up the whole thing into one vba sentence

    mydatevalue = cdate(left$(ProdDate,4) & "/" & mid$(proddate,5,2) & "/" & mid$(proddate,7,2))

    however I should say that unlike Izy's code (which always works, mine comes untested, and therefore could fail)

  6. #6
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1
    Thanks for all your help folkes

    Gareth, believe me, I'm on a six month contract, it took me 3 month just to get the view, I think my contract would run out by the time I got the format changed in the pass through querie

    Izy, I went down your route thanks, which has got me flying, but i will try and string it all together like healdem suggested, either way thanks for your help


  7. #7
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Red face 3 months

    Quote Originally Posted by MarkWhyte
    it took me 3 month just to get the view
    Wow! Any jobs going there?

    Give us a shout if you need any more help mate.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  8. #8
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1
    Will do thanks

Posting Permissions

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