Dan560
asked on
Convert string to day of week crystal reports
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
Thanks jtoutou
I pasted your function in and I get the message:
The keyword As is missing.
Can you help?
I pasted your function in and I get the message:
The keyword As is missing.
Can you help?
YOu have to call the function
ConvertTimestamp("YourUnix TimeStamp" )
ConvertTimestamp("YourUnix
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)
And because you need the day call the function like :
Me.Label1.Text = WeekdayName(Weekday(Conver tTimestamp (111321153 2)))
Me.Label1.Text = WeekdayName(Weekday(Conver
Try this:
DayofWeek(Date(yourdatetim e))
DayofWeek(Date(yourdatetim
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(inpu t[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(yourstringfieldnameher e,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)
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(inpu
2. An easy one. You know the day starts at position 4 for a length of 2:
Mid(yourstringfieldnameher
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)
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.
ASKER
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
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
What is not working? It says Thu 12-08. Thursday was the 12th of august.
ASKER
I need re-produce that report. That was not done in crystal
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.
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.
13598 did youtry mine?
ASKER
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
As I said I am completely new to this, so this could take some time. Sorry again
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,Your TimeStampC lumn,'1970 0101 05:00:00:000'))
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,Your
ASKER
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
I've just looked at the format of the unix time stamp and for example it looks like this 1,270,072,800.00
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
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
ASKER
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
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
ASKER
I'm using the formula in crystal
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}-1 8000, #1/1/1970#)
dateadd (“s”,{opencall.logdatex}-1
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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.
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.
ASKER
Thanks peter57r,
I know this looks stupid..but it's now working.
I really appreciate the help from everyone.
http://www.codeproject.com/KB/cs/timestamp.aspx