Another Date formatting question YYYMMDD to dd/mm/yyyy (1 Viewer)

[Little_Anj]

I know this seems to have been asked numerous times before, and maybe I am being stubborn with which solution can actually help me.

I cannot help it, in my mind mm/dd/yyyy is an incorrect way to display a date!! (I know, I know), but that seems to be all of the solutions I can find.

(I read somewhere that access is by it's nature is in mm/dd/yyyy format, however my database is in dd/mm/yyyy and seems to work correctly.)

So my question...
I am importing data with the date as a text YYYYMMDD, into a "dumping table".
I would like to append it to another table with the correct date format of dd/mm/yyyy as an actual date (for a date field).

Any tips or suggestions are welcome.

Thanks in advance.

 

[John Big Booty]

You should be able to use the DateSerial() function to convert the text to a date and you can then apply the Format (with Dates) function to convert that result to your preferred format.

You may also find the discussion here to be of interest.

 

[pr2-eugin]

You have to create a new field on the table for now call it TempDateField with Type Date/Time and then save the table. Create an UPDATE Query, something like..

UPDATE [COLOR=Blue]yourTableName[/COLOR]
SET TempDateField = Format(DateSerial(Left([COLOR=Blue][yourCurrentStringDateField][/COLOR], 4), Mid([COLOR=Blue][yourCurrentStringDateField][/COLOR], 5, 2), Right([COLOR=Blue][yourCurrentStringDateField][/COLOR], 2)), "dd/mm/yyyy")

However you can also create a small function in a Module and use the method to be called from your Query too...

Public Function changeToDate(strDate As String) As Date
[COLOR=Green]'-------------------------------------------------------------------------
'   A Function that will take in a String
'   Considering that the Date String you obtain is of this format
'   20131201 (yyyymmdd), would return a Date in the Format
'   01/12/2013 (dd/mm/yyyy)
'
'   Input   : A Normal String, that needs to be Formatted as Date
'   Output  : A Date in the Desired Format
'   Example :
'   ? changeToDate("20130201")
'     01/02/2013
'--------------------------------------------------------------------------[/COLOR]
[COLOR=Green]'PRIOR TO CHANGE:    changeToDate = Format(DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2)), "dd/mm/yyyy")[/COLOR]
    changeToDate = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2))
End Function

So in your Query you can use it as..

UPDATE [COLOR=Blue]yourTableName[/COLOR] SET TempDateField = changeToDate([COLOR=Blue][yourCurrentStringDateField][/COLOR])

Blue bits need to change.. If you see closely, I have mostly used the functions that JBB has shown i.e. DateSerial, Format.. and some additional to strip the Text String to Date.. i.e. Right, Mid, Left..

If your regional settings are set properly you do not need to use the format (I guess !!), but I have added that just incase..

 

[Brianwarnock]

Hey guys Format produces a string, it is for display purposes, you would need to wrap a Cdate round the field to use it as a date.

Dateserial produces a date and displays it in the local format as selected in Windows

Also remember that all dates are stored as double with the integer part being the date and the decimal the time, the Formats are because we humans cannot figure that 41352 is 19/03/2013

Brian

 

[pr2-eugin]

Dateserial produces a date and displays it in the local format as selected in Windows

Thanks for confirming my assumption Brian..

 

[Little_Anj]

Thanks everyone for your responses.
I will have a play with them and let you know how I go!!

 

[Little_Anj]

Thanks Everyone, it has worked a treat!!

 

[Little_Anj]

If you can still continue to help, I have another import with a date format of ddmmyy. I have managed to change pr2-eugin suggestion to accommodate the different order.
Unfortunately the first 9 days of each month are dmmyy!!

I Found a solution posted by Pbaldy, to help pick import dates with less than 6 characters, to change only them.

UPDATE SigmaImportT SET SigmaImportT.TempDateField = Format(DateSerial(Right([SaleDate],2),Mid([SaleDate],2,2),Left([SaleDate],1)),"dd/mm/yy")
WHERE Len(SaleDate) < 6

However I do not know how or if I can get the second update to run in the same query

UPDATE SigmaImportT SET SigmaImportT.TempDateField = Format(DateSerial(Right([SaleDate],2),Mid([SaleDate],3,2),Left([SaleDate],2)),"dd/mm/yy")
WHERE Len(SaleDate) > 5

Any suggestions?

 

[Brianwarnock]

Forget the criteria do the test with formula, the values for Len can be 5 or 6 so

Tempdatefield = iif(len(saledate)=6,formula for6,formula for 5)

Brian

 

[Little_Anj]

Forget the criteria do the test with formula, the values for Len can be 5 or 6 so

Tempdatefield = iif(len(saledate)=6,formula for6,formula for 5)

Brian

Thanks Brian!!

It's funny, I am always in Excel making amazing reports with flashy macros & elaborate formulas, one click buttons, and forever going over my VBA code... I never think to apply the same ideas like you mentioned above to replicate my favorite "IF" statement from excel!!

You are all a wealth of knowledge and I am really enjoying learning how to best use access