none
MS Access SQL Format function not rendering correctly RRS feed

  • Question

  • Hi Folks -

    I have the following MS Access SQL query:

    SELECT DISTINCT
    	'Changeprop' AS [PARAM1]
    	,'TREX-WorkingVersion' AS [PARAM2]
    	,'Partner' AS [PARAM3]
    	,[SFDC_PTRPTP].[PARTNERSHIPID] AS [PARAM4]
    	,'Execution_Date' AS [PARAM5]
    	,Format([SFDC_PTRPTP].[PARTNERSHIP_EXECUTION_DATE], "ddmmmyyyy") AS [PARAM6]
    FROM [SFDC_PTRPTP]
    INNER JOIN [MDM_Partner_Detail_Reference] ON [SFDC_PTRPTP].[PARTNERSHIPID] = [MDM_Partner_Detail_Reference].[Name]
    	WHERE ( Format([SFDC_PTRPTP].[PARTNERSHIP_EXECUTION_DATE], "ddmmmyyyy")  <> [MDM_Partner_Detail_Reference].[Execution_Date])

    It's used to build an import file for a financial system. The strange thing is some of the dates are not formatting correctly. For instance,  [SFDC_PTRPTP].[PARTNERSHIP_EXECUTION_DATE] is a Date/Time column from a MSSQL DB with a value of 12/19/2013.

    However, when I run the above query, the result is displayed as:

    PARAM1    PARAM2    PARAM3    PARAM4    PARAM5    PARAM6
    Changeprop    TREX-WorkingVersion    Partner    PTP-10274    Execution_Date    19Dec0201

    Why would this be?

    Thank you!

    Friday, January 24, 2020 10:02 PM

Answers

  • Try changing the date formats to mm/dd/yyyy or remove date formatting completely
    • Edited by isladogs52 Friday, January 24, 2020 10:22 PM
    • Marked as answer by cdtakacs1 Saturday, January 25, 2020 9:04 AM
    Friday, January 24, 2020 10:21 PM

All replies

  • Try changing the date formats to mm/dd/yyyy or remove date formatting completely
    • Edited by isladogs52 Friday, January 24, 2020 10:22 PM
    • Marked as answer by cdtakacs1 Saturday, January 25, 2020 9:04 AM
    Friday, January 24, 2020 10:21 PM
  • Ah, this ended up being a data type issue. Thank you for the suggestion, it helped me nail it down.
    Saturday, January 25, 2020 1:44 AM