DateTime to Unix Time dynamically

[TrentSteel]

Hello All

I have had no problems converting a Unix time stamp to DateTime for grouping and display in reports.(thanks to this forum, really, rather than my own reporting prowess) Now, I have begun playing with an automated report generator and exporter. Unfortunatley, I have no idea how to set the Selection criteria dynamically so it takes the last full work day of records every time the report is run.

eg.
I am setting the report up to run for the first time at 6am EST on Sept 9th. How would I get it to report on these parameters in Unix format within the selection formula:

Tues. 9th report - 09/08/03 00:00:01 to 09/08/03 23:59:59
Wed. 10th report - 09/09/09 00:00:01 to 09/09/03 23:59:59
etc.

Also, how would I account for weekends, b/c on Monday morning, I would like to be pulling Friday's data. Any ideas would be greatly appreciated

TIA

 

[Madawc]

For Crystal 8.5, try DateAdd. This allows you to add a given number of hours, days, weeks or whatever, and your 'Help' index should give details. Something like
DateAdd("d", -6, {your.date})
would give you a day six days before

For weekends, get the day-of-week, Saturday is 7 and Sunday is 1. Use a formula field containing
DatePart ("w" {your.date})
The resultant number tells you which day it is.

Madawc Williams
East Anglia, Great Britain

 

[TrentSteel]

Still working on this...update:

Thx Madawc for the help,but now I have run into an related problem that I think somone may be able to help me with. Can anyone see a reason why this selection formula would be timing out?

//if monday, go back 3 days for info, else 1 day and greater

if DatePart ("w",CurrentDate) = 2 then

//converting date based on "if" to Oracle TimeStamp

{SUBMITTED_ON} in datediff("s",DateTime (1970,01,01,00,00,01), DateAdd("h", -4, dateadd("d", -3, CurrentDateTime))) to datediff("s",DateTime (1970,01,01,00,00,01), DateAdd("h", -4, dateadd("d", -1, CurrentDateTime)))
  
else
 
{SUBMITTED_ON} >= datediff("s",DateTime (1970,01,01,00,00,01), DateAdd("h", -4, dateadd("d", -1, CurrentDateTime)))

Is there some reason why this wont' run as a Select Expression? Can anyone see a different way of doing this? Any help would be greatly appreciated

TIA

TS

 

[Madawc]

I'd separate out the codes as formulas and test them, to see if something is different from what you expected.

Madawc Williams
East Anglia, Great Britain

 

[lbass]

I think if you do the conversion to datetime separately, all you need to do for your select statement is something like this, since you are including all times of the day:

if DatePart ("w",CurrentDate) = 2 then
{yourconverted.datetime} in currentdate-3 else
{yourconverted.datetime} in currentdate-1

Your formula above doesn't work, at least in part, because datediff returns a number not a date.

-LB