Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2016
    Posts
    3

    Question Unanswered: Convert yyyy-mm-dd hh:mm:ss.sss to mm/dd/yyyy

    Hi:

    I am trying to convert text dates in yyyy-mm-dd hh:mm:ss.sss format into mm/dd/yyyy date/time format.

    I have tried the following in a Access 2010 query:

    Code:
    SELECT DateSerial(Right(PreviousDate,12),Left(PreviousDate,4),Mid(PreviousDate,6,2)) AS PrevDate
    FROM AllPostedDates;
    This yields #Error

    Any help, please? I'm working under a tight deadline.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    test if the value is a date (use the isdate function), within an IIF and use the cdate function

    so thats something like
    Code:
    iif( isdate(previousdate),cdate(previousdate), null)
    but of course the real fix is to store dates in a datetime column, but you knew that already didn't you

    |once its a date value then yo9u can do whatever the heck you like with that cvlaue and format it properly using ISO or RoW dates as opposed to the abomination that is the US centric mm/dd/yyyy
    Last edited by healdem; 06-23-16 at 09:18.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2016
    Posts
    3
    Thanks healdem,

    Unfortunately those who create the data don't necessarily use good database structure logic when they do so. So then some unsuspecting person has to "fix" the issues. Or in a worse case, make the problematic system "work" because of tight deadlines Lucky me.

    It looks like the dates failed the test, because the output query pulled up nothing which probably means I should just start from scratch on this date issue.

    1. I have a table with a column named PostedDates.

    2. The PostedDates data look like this: 2016-05-26 00:00:00.000.

    3. I need this date to look like this AND be formatted as a date, so my subsequent queries will not be problematic: 05/26/2016.

    4. I have tried right(), mid(), and left() functions. The closest I have gotten to being successful is this:

    Code:
    SELECT MID(PreviousDate,6,5) &"-"&LEFT(PreviousDate,4)
    FROM qryPaymentPostedDate
    (And things went downhill after this...)

    5. I have tried wrapping the above code with DateSerial, and get #Error for the output for all dates.

    6. Which is probably why the test you suggested failed.

    7. What should I do now? I don't have the power to alter the way the date is formatted as it is derived in a format that works for someone else, and I am expected to wrestle with it on my end. Again, lucky me

    8. A big Thank You! If you made it this far trying to help me!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off the 'right' way to resolve this problem is to store the date in the correct datatype, ie datetime NOT text/String. Even if you cannot fix that it still needs raising so that whatever twit dcidced to use text/String never ever does it again.

    if even if you can't then coerce the value to be date
    there are several ways of doing this., by far the best is to use the cdate function.
    ..but because muppet central has allowed a datataype where anything could be in those columns you need to be a bit more savvy.

    as to what code to use, here's a suggestion did you actually think to try what was suggested?
    if you coerce the value to be a datetime column (or datetime value then defacto afterwards you can display it as anything you like) depending on how many queries you have that need to use the data as a date it will make more sense to convert once. If the chief muppet who designed this table has doen such things once you can bet your bottom dolalr that he/she/it will have done similar things elsewhere. befire they are allowed anywhere near designing a table in future they need to learn a teensy weensy bit about db design.

    to leave it as it is persists bad design, cause problem each and every time you want to use the data and worse of all totally banjaxes using the datetime functions, without converting from string to date each and every time you do it.

    solutions to this are
    1) accept that chief muppet is still a chief so don't upset their little dreamland and insert a new column into the table, run a query that stuffs the string vlaue into the new column. so thats
    Code:
    update mytable set mynewcolumn = cdate(PreviousDate) where isdate(PreviousDate) = vbtrue and isnull(mynewcolumn)
    that query will attempt to set the value of mynewcolumn to the value of previousdate if previousdate is a date AND mynewcolumn is null
    2) chicken out of fixing the problem, and putting a plaster / band aid on the problem and say no more let someone else following on from you pick up th pieces and wonder why you didn't fix the problem by altering your query so that it does the date conversion. leastaways Im expecting qryPaymentPostedDate to be a query so you coudl use the date there
    3) write a function that handles the text value and returns the components you want. but why you'd want to do that seeign as you ave a perfectly good function (cdate) already beats me
    4) if you must persist with frigging the problem rather than fixing the problem then it will be somethign like
    Code:
    mid(mycolumn,6,2) & "/" & mid(mycolumn,9,2) & "/" & left(mycolumn,4)
    ..that will return a string so you will still need to use the cdate function to coerce it into a date
    and you will still need to test the incoming value is a date
    Code:
    iif(isdate(cdate(mycolumn,mid(mycolumn,6,2) & "/" & mid(mycolumn,9,2) & "/" & left(mycolumn,4),
    ,null))[/code]

    your colleague who IS prepared to put up with this needs to go away and learn about database, he /she/it should not have accepted this in the first place/ but not doing their job they have passed what starts as a minor initialistaion problem into an ongoing development problem. the cost of fixing this early is trivial the cost of fixing further down the line expands each and everyt time someone ignores it. the cost to your reputation at work also develops over time as you are forcred to do more and more intricate and fiddly transformations in queries, hit hurdles on designing reports/forms struggle with reproting, have a pig of a time doing comapartives all because some muppet either didn't understand their job or too the easy way out. There is rarely an excuse to perpetuate crap design, and lets be frank this is a prime example of crap design

    either fix it and get rid of the crap
    or modify it in a query as close as possible to the source problem. if doing so an approach would be to have a query (a view if you will) that maps to each and every base table that has this bastardisation mapping each column to a new column in and fix the data errors there.

    You also need to understand that when dealing with dates the means of storage is not tied to the means of display
    bear in mind there are several major date formats
    ISO: as your data is yyyy/mm/dd hh:mm:ss
    US: as you ant your data mm/dd/yyyy hh:mm:ss
    rest of the world dd/mm/yy hh:mm:ss
    bear in mind different countriues use different separators some use / some - some spaces.
    BUT inside the db, assuming the designer has doen their job a datrabse stroes a datre time value in some numeric format. Access JET, IIRC, uses the number of days since 31/12/1899, the proportion to the right of the decimal palce is the proportion of the day so 0 = 00:00:00 on that day, 0.5 = 12:00:00, 0.75 = 18:00:00 and (1/(24*60*60) represents one second

    the real reason for pressing you to do the job properly is that having coerced your data intot he correct fdormat you then dont' have to frig around when it comes to sorting and grouping, although the ISO format does lend itself better to eorting that the US and its especially better than the rest of the world format.

    another reason to fix this is if your work may be used by somebody who doesn't use the US format and whose NLS are not US then that can be a subtle source of problems going forward. the answer is fix it, pay the short term pain to fix it and move on.
    Last edited by healdem; 06-23-16 at 09:55.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2016
    Posts
    3
    Took most of my thoughts right out of my head. Unfortunately, I'm new and have no clout/say so. I'm at the mercy of the powers that be. I do voice concerns, but...well...here I am.

    I tried the first code you suggested and it returned no results. I am in the process of implementing your subsequent suggestions and will follow-up on how the steaming pile of dysfunction works out.

    Thanks again!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Easiest fix for now is to create a view (a query) which converts the string into a datetime. Eg
    Code:
    Select my, column, list, iif (isdate (acolumn), cdate (acolumn),null) as dvacolumn from mytable
    Save that query as say view_mytable

    Then use view_mytable instead of the ubderlying table, that way round dvacoulmn should be a date timevalue or NULL.

    Change tge names of the table, columns as required
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    Use the expression: DateValue(PreviousDate) to convert the ISO style date into mm/dd/yyyy value or whatever way you would like to format it and display.

    If you need both date and time together then the expression: DateValue(PreviousDate)+TimeValue(PreviousDate) or CDate(PreviousDate) will convert the value of Date & Time in Date/Time Format. Modify your Query accordingly.
    Last edited by apr pillai; 06-25-16 at 06:30. Reason: addition of text
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

Posting Permissions

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