Highlighted

ColdFusion is showing one previous date when I fetch it from the database

Community Beginner ,
Feb 27, 2020

Copy link to clipboard

Copied

when I am fetching the date from the database it's showing one date before.

for example - 

if date in the database is - 2020-02-26

then when i fetch to my coldfusion using select date from db it shows - 2020-02-25.

 

I checked for the now() which is showing correct.- showing accurate now date and time.

 

issue is only when i am fetching it from the database.

 

TOPICS
Database access

Views

855

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

ColdFusion is showing one previous date when I fetch it from the database

Community Beginner ,
Feb 27, 2020

Copy link to clipboard

Copied

when I am fetching the date from the database it's showing one date before.

for example - 

if date in the database is - 2020-02-26

then when i fetch to my coldfusion using select date from db it shows - 2020-02-25.

 

I checked for the now() which is showing correct.- showing accurate now date and time.

 

issue is only when i am fetching it from the database.

 

TOPICS
Database access

Views

856

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
Feb 27, 2020 0
LEGEND ,
Feb 27, 2020

Copy link to clipboard

Copied

CFDUMP the query results, and manually compare to what you see in the database.  Something, somewhere, is altering the output.  If the dump is doing this, something is really incorrect.  If the dump is showing what's in the database, then something is altering your output.

 

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...
Feb 27, 2020 0
Community Beginner ,
Feb 27, 2020

Copy link to clipboard

Copied

Thanks man, for the reply !

 

I checked it using dump also - It is showing incorrect in cfdump too...

 

 
 

Capture.PNG

 

on the above I have dump the query,

below is the actual result from database.

 

2019-12-29
2019-12-29
2019-12-25
2019-12-15
2019-12-12
2019-12-11
2019-12-10
2019-12-10
2019-12-09
2019-12-09
2019-12-05
2019-12-04
2019-12-03
2019-11-28
2019-03-31
2019-03-31
2019-01-31
2019-01-29
2019-01-10
2019-01-09

 

 

strange thing is same is working fine on my local CF 2018.

 

Issue is only on azure instances.. I checked the timestamp also which is equal for both azure instance and mysql database.

 

 

 

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...
Feb 27, 2020 0
LEGEND ,
Feb 28, 2020

Copy link to clipboard

Copied

Occam's Razor.  It seems that the common denominator with the erroneous output is AWS.  Have you submitted a trouble ticket with Amazon?

 

I hate to say it, but I have _zero_ experience with AWS, so can't really think of anything else.  Perhaps someone else with AWS experience can chime in.  But if it works locally and not on AWS, it's most likely an AWS issue.

 

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...
Feb 28, 2020 0
Adobe Community Professional ,
Mar 01, 2020

Copy link to clipboard

Copied

What is the datatype of the column in which the "dates" are stored?

A test:

What happens if you assumed what you were selecting were strings, then cast to date later on? Something like this:

<cfquery name="dateTest" datasource="yourDSN">
    select snap_date
    from fcst_rv_diff_snap
</cfquery>
<cfoutput query="dateTest">
    #parsedatetime(dateTest.snap_date)# <br>
</cfoutput>

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...
Mar 01, 2020 0
LEGEND ,
Mar 02, 2020

Copy link to clipboard

Copied

Along the lines of what BKBK suggested, you can also CAST() the value to a datetime datatype within the database query.  Depending upon what flavour of database server you're using, it should be something like:

SELECT CAST(snap_date as datetime) snap_date

FROM fcst_rv_diff_snap

 

If you're using Oracle, it would be:

SELECT TO_DATE(snap_date,'yyyy-mm-dd') as snap_date

FROM fcst_rv_diff_snap

 

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...
Mar 02, 2020 0
New Here ,
Mar 12, 2020

Copy link to clipboard

Copied

I'm having the same issue on my ColdFusion 2018 servers.  Using a MySQL 5 database, when I query via MySQL Workbench, I see the correct date (3/28/2020 for example), however, when I output it OR cfdump the query, it says the date is the previous day's date (3/27/2020 for example).

 

Any info as to what could be the problem is much appreciated.

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...
Mar 12, 2020 0
New Here ,
Mar 13, 2020

Copy link to clipboard

Copied

I figured out the issue.  Turns out that whenever our hosting provider does any updates or reboots the database server, the timezone resets to Greenwich Mean Time.

 

Running this in MySQL confirms that it's set back to "SYSTEM":

SELECT @@global.time_zone, @@session.time_zone;

 

Our server is in the Central Timezone, so running this SQL updates the timezone:

SET GLOBAL time_zone = 'America/Chicago';
SET SESSION time_zone = 'America/Chicago';

 

Hopefully this may help someone else later down the line.

 

Cheers!

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...
Mar 13, 2020 1
BKBK LATEST
Adobe Community Professional ,
Mar 13, 2020

Copy link to clipboard

Copied

Ah, what do you know!

Thanks for sharing that, danny_gunter . Sort of makes sense, with hindsight.

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...
Mar 13, 2020 0