• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Select as date

Participant ,
Jan 25, 2019 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'}

Views

1.4K

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
community guidelines

correct answers 1 Correct answer

Community Beginner , Jan 28, 2019 Jan 28, 2019

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

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

Votes

Translate

Translate
LEGEND ,
Jan 25, 2019 Jan 25, 2019

Copy link to clipboard

Copied

You answered your own question.  DateFormat().

V/r,

^ _ ^

Votes

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
community guidelines
Participant ,
Jan 25, 2019 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'}

Votes

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
community guidelines
Community Expert ,
Jan 26, 2019 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

Votes

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
community guidelines
Participant ,
Jan 28, 2019 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.

Votes

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
community guidelines
Community Beginner ,
Jan 28, 2019 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. 

Votes

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
community guidelines
Participant ,
Jan 28, 2019 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'}

Votes

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
community guidelines
Community Beginner ,
Jan 28, 2019 Jan 28, 2019

Copy link to clipboard

Copied

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

Votes

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
community guidelines
Participant ,
Jan 28, 2019 Jan 28, 2019

Copy link to clipboard

Copied

MS SQL 2008

Votes

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
community guidelines
Community Beginner ,
Jan 28, 2019 Jan 28, 2019

Copy link to clipboard

Copied

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

Votes

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
community guidelines
Participant ,
Jan 28, 2019 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.

Votes

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
community guidelines
Community Beginner ,
Jan 28, 2019 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'

Votes

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
community guidelines
Participant ,
Jan 28, 2019 Jan 28, 2019

Copy link to clipboard

Copied

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

Votes

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
community guidelines
Community Beginner ,
Jan 28, 2019 Jan 28, 2019

Copy link to clipboard

Copied

LATEST

Great, glad its working! 

Votes

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
community guidelines
Resources
Documentation