Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert string to day of week crystal reports

Posted on 2010-08-13
25
Medium Priority
?
943 Views
Last Modified: 2013-11-26
Hi,

I'm completey new to programing and crystal reports. So I apologise in advance if this doesnt make any sense.

I need to create a weekly report. We run a helpdesk system and I need to show the number calls logged for each day of the week in that report.

My database records the date of each call as a unix time stamp.

I need convert this unix time stamp so it displays the day. I'm not sure if this is the right solution or if it's possible. I would appreciate if someone can give me some guidance on this.
0
Comment
Question by:Dan560
[X]
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
  • 8
  • 8
  • 6
  • +2
25 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 1600 total points
ID: 33431602
Assuming your timestamp is expressed in seconds then you can get the correct windows date using..

DateAdd("s", {table.timestampfield}, #1/1/1970#)

So the day would be..

weekdayname(DayOfWeek (DateAdd("s", {table.timestampfield}, #1/1/1970#)))
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33431691
here is how to convert UNIX timestamp to System.DateTime
http://www.codeproject.com/KB/cs/timestamp.aspx
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33431719
try also this function
Function ConvertTimestamp(ByVal timestamp as Double) As DateTime
 Return New DateTime(1970, 1, 1, 0, 0, 0).AddSeconds(timestamp).ToLocalTime()
End Function

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Author Comment

by:Dan560
ID: 33431794
Thanks jtoutou

I pasted your function in and I get the message:

The keyword As is missing.

Can you help?
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33431814
YOu have to call the function
ConvertTimestamp("YourUnixTimeStamp")
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33431851

Function ConvertTimestamp(ByVal timestamp as Double) As DateTime
 Return New DateTime(1970, 1, 1, 0, 0, 0).AddSeconds(timestamp).ToLocalTime()
End Function
Me.Label1.Text = ConvertTimestamp(1113211532)

Open in new window

0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33431934
And because you need the day call the function like :
Me.Label1.Text = WeekdayName(Weekday(ConvertTimestamp(1113211532)))
0
 
LVL 16

Expert Comment

by:13598
ID: 33432132
Try this:
DayofWeek(Date(yourdatetime))
 
0
 
LVL 16

Expert Comment

by:13598
ID: 33432380
I think you need the day of the week right as in today is 08/13/2010 so you would need 13?
If so in your formula in your crystal report you have 2 options:
1. A complex one ( you would substitute "08/13/2010 11:00:00am" with your string fieldname that contains the unixt datettimestamp :
local stringvar input := "08/13/2010 11:00:00am";
day(Date(datetime(val(input[7 to 10]),val(input[1 to 2]),val(input[4 to 6]),val(input[11 to 12]),val(input[13 to 14]),0)))
2. An easy one. You know the day starts at position 4 for a length of 2:
Mid(yourstringfieldnamehere,4,2)
3. I do believe unix datetimestamp format is 00/00/0000 for the date part so even if it is day 1 solution 2 would still work because it would be 08/01/2010 but if that is not the case you could do it based on the slash separator which is a little more complex:
if mid( "08/13/2010 11:00:00am", instr("08/13/2010 11:00:00am","/") +2,1) = "/" then
mid( "08/13/2010 11:00:00am", instr("08/13/2010 11:00:00am","/") +1,1)
else
mid( "08/13/2010 11:00:00am", instr("08/13/2010 11:00:00am","/") +1,2)
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33432539
The UNIX timestamp represents the time measured in number of seconds since the Unix Epoch (1st of January 1970 00:00:00 GMT), and is well-known to PHP-developers.
0
 
LVL 2

Author Comment

by:Dan560
ID: 33432625
Sorry I can't get it to work. I thinks it's down to lack of understanding on my behalf.

I've attached  a report that I need to reproduce. I need to convert the time stamp into a day of the week meaning Monday,Tuesday Wednesday etc...

I re read my question and I'm sorry I didnt make this clear.

Thanks

example.png
0
 
LVL 16

Expert Comment

by:13598
ID: 33432662
What is not working? It says Thu 12-08. Thursday was the 12th of august.
 
0
 
LVL 2

Author Comment

by:Dan560
ID: 33432676
I need re-produce that report. That was not done in crystal
0
 
LVL 16

Expert Comment

by:13598
ID: 33432694
OK what exactly is not working then?
Look at this:
http://www.chrisedwards.ws/2007/01/19/crystal-reports-converting-unix-time-stamp/
 
Also did you try  peter57r solution. I didn't double-check it but it looks like that is the solution to get the dayofweek.
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33432713
13598 did youtry mine?
0
 
LVL 2

Author Comment

by:Dan560
ID: 33432718
I'm going to retry and post back the results.

As I said I am completely new to this, so this could take some time. Sorry again
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33432821
What Kind Of database are you using

If you are using SQL Databaase create a view with your table and create a new column to convert the unixtimestamp ...using
DATENAME(dd,dateadd(s,YourTimeStampClumn,'19700101 05:00:00:000'))
0
 
LVL 2

Author Comment

by:Dan560
ID: 33432844
I'm using mysql,

I've just looked at the format of the unix time stamp and for example it looks like this 1,270,072,800.00
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 33434548
Are you entering the date range you want to compare against?

The formulas provided in the first comment will convert the Unix time to a date and the day of the week.

Did you try them in a formula in Crystal or are you trying to do this in the SQL?

mlmcc

0
 
LVL 2

Author Comment

by:Dan560
ID: 33435782
when I use this formula

weekdayname(DayOfWeek (DateAdd("s", {table.timestampfield}, #1/1/1970#)))

i get this message:

The result of the section must be a a bolean

I have replaced table.timstampfield with my own table. It's called opencall.logdatex
0
 
LVL 2

Author Comment

by:Dan560
ID: 33435784
I'm using the formula in crystal
0
 
LVL 16

Expert Comment

by:13598
ID: 33436560
Start by using this in your formula. It will not give you the name of the weekday yet but that way we can see what we are getting and where the issue is . First try this and let me know which value you get or what error you get:
dateadd (“s”,{opencall.logdatex}-18000, #1/1/1970#)
0
 
LVL 16

Assisted Solution

by:13598
13598 earned 400 total points
ID: 33436592
Where are you placing this formula? Place is on your detail section until we know we are getting the correct value.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 33436811
"The result of the section must be a a bolean "

means you are in the wrong place.

You should be creating a new formula field.

The message suggests you are in a record selection formula or conditional format button.


0
 
LVL 2

Author Comment

by:Dan560
ID: 33437598

Thanks peter57r,

I know this looks stupid..but it's now working.

I really appreciate the help from everyone.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In this post we will learn different types of Android Layout and some basics of an Android App.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

660 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