Solved

My date in Crystal is one day less than the date in the database, how do I fix this?

Posted on 2011-09-15
20
399 Views
Last Modified: 2012-06-27
I have written a report, and when I place the date fields into the report, the date is coming up 1 day different:

Database = 10/2/2010
Crystal = 9/2/2010

How do I fix this and why is it happening??
0
Comment
Question by:spectrum17
  • 9
  • 7
  • 3
20 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 36547086
Is it a datetime field?

I have run reports where either the database connection or Crystal converted the time from GMT to local but in your case I would think that would make the times from Crystal later rather than earlier.

If they are datetime field can you format them to show the time and see what the difference is.

mlmcc
0
 

Author Comment

by:spectrum17
ID: 36547107
Hi mlmcc

Yes it is a date time field. I've formatted it to show the times:

Crystal: 1:00 pm 9/2/2010
Database: 0:00 10/2/2010

Any ideas how to fix this to show the date the same as in the database?

0
 
LVL 77

Expert Comment

by:peter57r
ID: 36547177
What expressions are you using to get each of these datetime values?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36547193
The time makes sense.  It is subtracting 11 hours.

WHat timezone does  the computer have set as local?

mlmcc
0
 

Author Comment

by:spectrum17
ID: 36547206
Peter57r,

I am just using the field from the table. I changed the format to see what time was in the date field.
0
 

Author Comment

by:spectrum17
ID: 36547209
mlmcc,

I am unsure of the timezone on the database computer. I've sent the administrator an email to check what timezone it is using.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36547211
You might also check the timezone of the database server.

mlmcc
0
 

Author Comment

by:spectrum17
ID: 36547216
Will do. Could this problem be fixed using a DateAdd formula? ie

Dateadd("H",+11,{datefield})
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36547222
I may have the time zone thing backwards.

See this article.  According to it Crystal shows the datetime tht is in the database whereas the system will correct the datetime to reflect the local (computer) timezone

http://community.landesk.com/support/docs/DOC-3447

If that is the case then it makes sense
Crystal is GMT
Database shows local time which if the time zone in your question is orrect New SOuth Wales.  You are currently 10 hours ahead but in February (no DST) you would be 11 hours ahead thus the database adds 11 hours to what Crystal shows and you get the midnight time.

mlmcc
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 100

Expert Comment

by:mlmcc
ID: 36547267
You could fix it that way except that you have to account for daylight savings time and add 10 hours during that time frame.

mlmcc
0
 

Author Comment

by:spectrum17
ID: 36547282
Thanks mlmcc.

I had a look at the link, but couldn't bring up the pictures.

Does this mean that the DateAdd formula would work, but only for the daylight savings months?

Also, is the DateAdd formula the way to fix it?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36547285
Michael, Can I ask why that article is relevant here.
Do you know more about this poster than is said in the question?
 
0
 

Author Comment

by:spectrum17
ID: 36547286
Sorry, I just saw your latest post.

Given that the DST dates aren't set in stone, is there a formula that deals with the daylight savings dates?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36547303
peter - I have no knowledge other than the question.  I included the link to show this is a common issue and that it seems it is the database display that cmpensates for the time zones and not Crystal as my original post indicated

spectrum - I don't know of a good formula for accounting for DST.  Especially since the dates have changed recently.  

mlmcc
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 36547406
Ok.

Then I think what I want to know from Spectrum17 is how you are looking at the 'time in the database'?
Are you looking at it through an application form/screen or are you looking at the raw data in the database.

I don't see how Crystal can report anything other than the raw data ( without the use of formulas) so I think I am probably saying the same as mlmcc - that it is the source application which adjusting the display of the time.

That doesn't address the question of which value is correct for the report.
Crystal has a ShiftDateTime() function which allows you to adjust datetime fields for time zones but you would would have to be sure that you apply the function consistently throughout the report.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36547422
2006 and before
  Start - first Sunday in April
  End - Last Sunday in October

2007 and later
  Start - Second sunday in March
  End - First Sunday in November

DST Start

 
Local DateTimeVar DST_Start;
Local NumberVar DST_StartDay;

if year({tblHoursWorked2011.Started}) <=2006 then
(
 DST_StartDay := (2 + (6 * year({tblHoursWorked2011.Started})) - (floor(year({tblHoursWorked2011.Started}) / 4))) mod 7 + 1;
 
 DST_Start := DateTime(Date(year({tblHoursWorked2011.Started}), 4, DST_StartDay),Time(2,0,0));
)
else
( 
 DST_StartDay := 14 - (floor (1 + year({tblHoursWorked2011.Started}) * 5 / 4) mod 7);
 
 DST_Start := DateTime(Date(year({tblHoursWorked2011.Started}), 3, DST_StartDay),Time(2,0,0));
);
DST_Start

Open in new window



DST End
 
Local DateTimeVar DST_End;
Local NumberVar DST_EndDay;

if year({tblHoursWorked2011.Started}) <=2006 then
(
 DST_EndDay := (31 - ((year({tblHoursWorked2011.Started}) * 5 / 4) + 1) mod 7); 
 
 DST_End := DateTime(Date(year({tblHoursWorked2011.Started}), 10, DST_EndDay),Time(2,0,0));
)
else
( 
 DST_EndDay := 7 - (floor (1 + year({tblHoursWorked2011.Started}) * 5 / 4) mod 7);
 
 DST_End := DateTime(Date (year({tblHoursWorked2011.Started}), 11, DST_EndDay),Time(2,0,0));
);
DST_End

Open in new window


mlmcc
0
 

Author Comment

by:spectrum17
ID: 36547611
whoa! that's a monster of a formula mlmcc! I'm wondering how I would incorporate that into the report? I have 2 date fields in the report (start date & expiry date).

also, I heard back from the administrator of the database - apparently the date format is:

"Universal Time Coordinated (UTC) time. The financial company is linked to a time zone and based on this the local time is calculated from UTC time. "

Does this make any sense to you?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36550153
Many databases storre time a UTC which is the same as GMT except it doesn't observe daylight savings.  That makes it so if you change time zones you don't have to figure out the time shift.

You can use the new  ShiftDateTime()  function to change the date time to local.

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36967101
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now