Select as date

[bloodbanker]

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'}

[WolfShade]

You answered your own question.  DateFormat().

V/r,

^ _ ^

[bloodbanker]

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'}

[Dave Watts]

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

[bloodbanker]

Thanks Dave.  That didn't work either, but I'm sure it's me.  If MNCDate wasn't called from a query yet, how can I use cfset to modify it?

As for "void using query column names that don't follow the standard rules for CF variable names", I'm not sure what you mean?  These are the column names from my database.  I want them labeled different in the spreadsheet that is downloaded.

[BigRigMike]

You seem to be mixing coldfusion and SQL. 

Easiest way is to just select the column as is ( SELECT MNCDate, etc, etc, ) then within your cfoutput you would use:

#DateFormat(emaillist .MNCDate, 'mm/dd/yyyy')#

This will give you the desired output your looking for.

Otherwise, to pre-format the date in the query you need to look into the date formatting functions for the SQL server you are using. 

[bloodbanker]

BigRigMike​, thanks.  The query runs when the user clicks a download button and a file is downloaded as an Excel file, so I don't get a chance to format the date in the output.  That's why I'm trying to do it in the query.

In the database, it is stored correctly.

The database shows this: 2019-01-02

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

[BigRigMike]

What SQL are you running? MySQL, MSSQL, Etc? 

[bloodbanker]

MS SQL 2008

[BigRigMike]

Try This in your select: FORMAT(MNCDate, 'MM/dd/yyyy') as 'A - Datetime'

[bloodbanker]

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

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

[BigRigMike · Accepted solution]

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

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

[bloodbanker]

WOOHOO!!!  Thank you BigRigMike​, that worked.

[BigRigMike]

Great, glad its working!