Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
402 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Formatting a Crystal Report 2 68
Need to learn crystal reports 7 74
Crystal Reports Subreport details 16 66
Installation package ASP.NET with Crystal Reports version 13 12 69
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

791 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