Page 1 of 1

Different Date in Database As Compared to Application

Posted: Thu Jul 30, 2015 11:22 am
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'

Re: Different Date in Database As Compared to Application

Posted: Thu Jul 30, 2015 11:39 am
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'.