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

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.

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,

^ _ ^

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>

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,

^ _ ^

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,

^ _ ^

It is happening with the azure VM only which resides on EAST US regoin. I checked the timestamp and both db server and VM belongs to the same timezone.

when I check the same on my local it is working fine but failing only on azure instance.