Convert dates to more conventional format

[didiomm]

Hi All:

I've inherited data that has to be put in a DB that has dates saved as texts and dates in yyyy-mm-dd hh:mm:sss formats.



For example, one dataset has this date saved as text: '4122016' which equals April 12, 2016. How do I convert this to the 8 digit format ("mm-dd-yyyy"?) Because eventually the dataset will jump up to 8 digits by virtue of October dates having to be inputted.

I would like to convert yyyy-mm-dd hh:mm:sss into an 8 digit date format as well.

I tried select left for the long format, but I need more parsing to get it to the 8 digit format and nothing I've tried has worked so far.

Any help would be appreciated!

[pbaldy]

You can use a combination of the Left(), Right() and Mid() functions along with DateSerial(), but you have to be very sure of the data. What if it was "1162016"; is that Jan 16 or Nov 6? Are days always input as 2 digits?

[didiomm]

Hi pbaldy:

Given the historical format for any dates that are the 1st of the month through the 9th of the month are always marked as

"01","02","03","04","05","06","07","08", and "09"

I have to assume that when January rolls around again that the date 1012017 is truly January 1, 2017 and 11012016 is truly November 1, 2016.
So when using the Left, Mid, and Right syntax would I add in the "0" for months 1(January) through 9(September)?

For example: 1012016 would be converted to 01-01-2016, but how do I code this date to dateserial and not just a string?

And how do I build in the dateserial syntax.

Regarding parsing the long date using Left, Mid, Right syntax, I tried this:

SELECT MID(qryPaymentPostedDate.PostedDate,6,5) &"-"&LEFT(qryPaymentPostedDate.PostedDate,4)
FROM qryPaymentPostedDate

It works great (thanks)! Again, is there a certain syntax to use to wrap the dateserial function around the above code to code it as a date?

Thanks for your help

[pbaldy]

Along the lines of:

DateSerial(Right(...), Left(...), Mid(...))

[didiomm]

I tried this

DateSerial(Mid([qryPaymentPostedDate].[PostedDate],6,5),&( "-" &),(LEFT([qryPaymentPostedDate].[PostedDate],4)), AS Expr1

but I keep getting "wrong number of arguments" error

[pbaldy]

Well, the function has 3 arguments: year, month and day.

https://support.office.com/en-us/art...a-93f2b046f503

[didiomm]

Still getting errors: could it be that the "Right ()" function needs to have some kind of trimming to exclude the hh:mm:ss.mis data? Because it seems that my query to modify the date with the suggested syntax

(SELECT DateSerial(Right(qryPaymentPostedDate.PostedDate,15),Left(qryPaymentPostedDate.PostedDate,4),Mid(qryPaymentPostedDate.PostedDate,6,2)) AS Expr1
FROM qryPaymentPostedDate;

is turning my date into this: 04-05-2016 00:00:00.000 instead of the mm-dd-yyyy I needed. I say "seems" because the resultset is nothing but #Error all the way to the end of the list.

Thanks for your help, pbaldy.

[pbaldy]

Well, if the data still looks like '4122016', your numbers don't make any sense. You'd want the right 4 for year, not 15.

[didiomm]

Thanks pbaldy. but the code I most recently posted about is for the dataset with the long format, not the dateset with the shorter format. Sorry for the confusion. I'm waist-deep in wading through this so you can imagine my confusion as well

If there were a way to format the results of my code in my second post (and reposted below) into being a date instead of text then I would be done, already!

SELECT MID(qryPaymentPostedDate.PostedDate,6,5) &"-"&LEFT(qryPaymentPostedDate.PostedDate,4)
FROM qryPaymentPostedDate