Skip to main content
B
bloodbanker
Known Participant
January 25, 2019
Answered

Select as date

  • January 25, 2019
  • 1 reply
  • 2479 views

I know I can format the output of a date using DateFormat, but when the user clicks the download button, it directly downloads an excel file.

I have a date field in MS SQL  It is not a datetime, but just date.  If I download the field directly I get {ts '2019-01-02 00:00:00'}.

I tried CAST(MNCDate as Date) AS 'Collection Date' but got the same thing.

Can I modify the way a date is displayed in the output from the query?  I want mm/dd/yyyy.

Thank you

{ts '2019-01-02   00:00:00'}
    This topic has been closed for replies.
    Correct answer BigRigMike

    Thanks, but it's the same error as DateFormat.

    'FORMAT' is not a recognized built-in function name.


    My fault, that may be a 2012+ function. Try this:

    CONVERT(VARCHAR(10), MNCDate, 101) AS 'A - Datetime'

    1 reply

    WolfShade
    WolfShade
    Legend
    January 25, 2019

    You answered your own question.  DateFormat().

    V/r,

    ^ _ ^

    B
    bloodbankerAuthor
    Known Participant
    January 25, 2019

    Thanks, I already tried that and get this error:

    'DateFormat' is not a recognized built-in function name.

    The full query is:

    <cfquery name="emaillist" datasource="#APPLICATION.DSN#">

    SELECT

            DateFormat(MNCDate, 'mm/dd/yyyy') As 'A - Collection Date',

            Unit As 'B - Unit Number',

            Employees.Last_Name As 'C - Staff Last Name',

            Employees.First_Name As 'D - Staff First Name',

            patient_last AS 'E - Patient Last Name',

            patient_first AS 'F - Patient First Name',

            Donor_MRN As 'G - Donor MRN',

            Recpient_MRN As 'H - Recpient MRN',

            Donortype As 'I - Donor Type',

            Donor_Weight As 'J - Donor_Weight',

            Recipient_Weight As 'K - Recipient Weight',

            CD34 As 'L - Precirculating CD34 Count',

            PCD34 As 'M - Product CD34 Count',

            C As 'N - Product Volume', 

            D As 'O - Volume Processed',

            prod_neut As 'P - Product % Neutrophils',

            I As 'Q - Product Hct',

            cd34eff As 'R - CD34 Efficiency'

    FROM

    Employees RIGHT OUTER JOIN

        MNC ON Employees.MNCStaff = MNC.Staff

    WHERE

    MNCDate >= <cfqueryparam value="#startdate#" cfsqltype="cf_sql_timestamp"> AND

    MNCDate <= <cfqueryparam value="#enddate#" cfsqltype="cf_sql_timestamp">

    ORDER BY

    MNCDate

    </cfquery>

    Also, when I send the data to the database I use this:

    <cfqueryparam cfsqltype="cf_sql_date" value="#Dateformat(Form.MNCDate, 'mm/dd/yyyy')#" null="#NOT len(trim(form.MNCDate))#" />

    The database shows this: 2019-01-02

    I can only seem to get this in Excel: {ts '2019-01-02 00:00:00'}

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    January 26, 2019

    DateFormat is a ColdFusion function, not necessarily a database function. You'd have to use it before your query, something like this:

    <cfset formattedMNCDate = DateFormat(MNCDate, 'mm/dd/yyyy')>

    <cfquery ...>

    SELECT formattedMNCDate AS 'A - Collection Date' ...

    That said, your database probably has a similar function that will format your dates as you want. Also, I would probably try to avoid using query column names that don't follow the standard rules for CF variable names. Of course, you may have a reason for doing this that I don't know about.

    Dave Watts, Eidolon LLC

    Dave Watts, Eidolon LLC
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices