none
Convert text field to formatted date ? RRS feed

  • Question

  • Am coding a query to read a date field from a mainframe table. The field is 8 characters in format YYYYMMDD. They use '99999999' to mean no end date.

    I need to convert the dates to MM/DD/YYYY format.

    I tried using the FORMAT(Datefield,"Short Date") but it gives me '#Num" in every field. I'd like to be able to use the FORMAT function instead of SUBSTR, can I do that with a non-date field?


    PS. I tried using CDate() inside the Format() function and that gave 'data type mismatch'. Then I tried the whole thing within an IIF(Datefield="99999999","",CDate(Datefield)) function and that did not work either. 
    Tuesday, April 4, 2017 4:39 PM

Answers

  • I ended up using this:

    SELECT MID([SYSUSR_RM_SEL_ALL Query].[REL/DATE],5,2) & "/" &
    RIGHT([SYSUSR_RM_SEL_ALL Query].[REL/DATE],2) & "/" &
    LEFT([SYSUSR_RM_SEL_ALL Query].[REL/DATE],4) AS [REL/DATE]

    could not get CDATE and FORMAT to work at all, kept getting 'Data Type Mismatch' errors.

    Tuesday, April 4, 2017 5:34 PM

All replies

  • I ended up using this:

    SELECT MID([SYSUSR_RM_SEL_ALL Query].[REL/DATE],5,2) & "/" &
    RIGHT([SYSUSR_RM_SEL_ALL Query].[REL/DATE],2) & "/" &
    LEFT([SYSUSR_RM_SEL_ALL Query].[REL/DATE],4) AS [REL/DATE]

    could not get CDATE and FORMAT to work at all, kept getting 'Data Type Mismatch' errors.

    Tuesday, April 4, 2017 5:34 PM
  • Format would not be able to identify that value as a Date. You would have to parse the string, as in your example, and use something like DateSerial to convert to an actual date first:

    Format(DateSerial(Left(strTestDate, 4), Mid(strTestDate, 5, 2), Right(strTestDate, 2)), "Short Date")


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, April 4, 2017 5:39 PM
  • Even after using CDATE() function?
    Tuesday, April 4, 2017 9:43 PM
  • Even after using CDATE() function?

    CDate would not be able to identify those number as dates either. As a matter of fact the IsDate function will return false on that eight digit format.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, April 4, 2017 10:06 PM