Highlighted

Select as date

Explorer ,
Jan 25, 2019

Copy link to clipboard

Copied

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'}
Correct answer by BigRigMike | Community Beginner

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

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

Views

360

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Select as date

Explorer ,
Jan 25, 2019

Copy link to clipboard

Copied

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'}
Correct answer by BigRigMike | Community Beginner

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

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

Views

361

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jan 25, 2019 0
LEGEND ,
Jan 25, 2019

Copy link to clipboard

Copied

You answered your own question.  DateFormat().

V/r,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 25, 2019 0
Explorer ,
Jan 25, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 25, 2019 0
Adobe Community Professional ,
Jan 26, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 26, 2019 0
Explorer ,
Jan 28, 2019

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 28, 2019 0
Community Beginner ,
Jan 28, 2019

Copy link to clipboard

Copied

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. 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 28, 2019 0
Explorer ,
Jan 28, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 28, 2019 0
Community Beginner ,
Jan 28, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 28, 2019 0
Explorer ,
Jan 28, 2019

Copy link to clipboard

Copied

MS SQL 2008

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 28, 2019 0
Community Beginner ,
Jan 28, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 28, 2019 0
Explorer ,
Jan 28, 2019

Copy link to clipboard

Copied

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

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 28, 2019 0
Community Beginner ,
Jan 28, 2019

Copy link to clipboard

Copied

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

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 28, 2019 0
Explorer ,
Jan 28, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 28, 2019 0
Community Beginner ,
Jan 28, 2019

Copy link to clipboard

Copied

Great, glad its working! 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 28, 2019 0