Different Date in Database As Compared to Application

Discussions related to Crystal Reports development.

Moderator: Lisa Ennis

Post Reply
tim.pageler
Posts: 10
Joined: Fri Jan 10, 2014 4:18 pm

Different Date in Database As Compared to Application

Post by tim.pageler »

We are running into a situation where the date represented in the Select application shows one value and the database is showing another. As an example, order #270-140701008 shows an Escrow Closed Date/Time in the application as '12/31/2014 06:47 PM'. In the database, however, it shows 2015-01-01 00:47:00.000 or '01/01/2015 12:47 AM'. Is there an explanation as to why this is happening?

Code: Select all

SELECT o.Number,e.ClosedDate
FROM SelectDb.pfm.[Order] o
	LEFT JOIN SelectDb.pfm.Escrow e ON e.RootId# = o.RootId#
WHERE o.Number = '270-140701008'
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Different Date in Database As Compared to Application

Post by Lisa Ennis »

Any dates in the database that have the time indicated (not 00:00:00.000) are stored as UTC. When you view those dates in the program they are being converted to the time zone of the client machine.

You can use the function dbo.ConvertTimeFromUTC in your queries to convert the UTC datetime stored in the database to the timezone you desire. When you specify the time zone, you need to use the full text of the time zone, such as 'Eastern Standard Time'.
Lisa Ennis
Senior Report Developer
SoftPro
Post Reply