• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

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

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
spectrum17
Asked:
spectrum17
  • 9
  • 7
  • 3
1 Solution
 
mlmccCommented:
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
 
spectrum17Author Commented:
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
 
peter57rCommented:
What expressions are you using to get each of these datetime values?
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
mlmccCommented:
The time makes sense.  It is subtracting 11 hours.

WHat timezone does  the computer have set as local?

mlmcc
0
 
spectrum17Author Commented:
Peter57r,

I am just using the field from the table. I changed the format to see what time was in the date field.
0
 
spectrum17Author Commented:
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
 
mlmccCommented:
You might also check the timezone of the database server.

mlmcc
0
 
spectrum17Author Commented:
Will do. Could this problem be fixed using a DateAdd formula? ie

Dateadd("H",+11,{datefield})
0
 
mlmccCommented:
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
 
mlmccCommented:
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
 
spectrum17Author Commented:
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
 
peter57rCommented:
Michael, Can I ask why that article is relevant here.
Do you know more about this poster than is said in the question?
 
0
 
spectrum17Author Commented:
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
 
mlmccCommented:
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
 
peter57rCommented:
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
 
mlmccCommented:
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
 
spectrum17Author Commented:
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
 
mlmccCommented:
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
 
mlmccCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now