Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hummy
Starting Member
32 Posts |
 Posted - 2008-11-13 : 17:19:27
|
| Hi,I wonder if someone can help me. Our CRM system that is used stores dates in the database rather oddly. Since Oct 26th (when the clocks changed) all dates in the database are stored as 2008-11-13 00:00:00, which is perfectly good for me as we are only interested in the date section. However prior to October 26th all dates were 1 hour out. So for example if i entered a date into the CRM system prior to 26th Oct (e.g 25th July 2008) it would store it as 2008-07-24 23:00:00. Now this is causing me quite a few problems. Becuase i have recently had a need to do a series of reports and as a result of which i export data from the live SQL Server to a reports datbase everynight. For some records now my data seems as though it's 1 day out, even though my CRM system reports it correctly. Can someone tell me the best way to handle such a thing so that any dates with 23:00:00 in them are converted to be the next days date. Is it best to introduce some trigger into my reports database to handle such a thing or is ther an inbuilt function i can use. Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-14 : 02:28:24
|
[code]DECLARE @Sample TABLE ( dt DATETIME )INSERT @SampleSELECT '2008-07-24 23:00' UNION ALLSELECT '2008-11-13 00:00'SELECT dt, DATEADD(DAY, DATEDIFF(DAY, '19000101', DATEADD(HOUR, 1, dt)), '19000101')FROM @Sample[/code]
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
|
|
|
How to work with UTC datetime fields.