Adding Time to date field

[jwinsten]

Hi,
I have a field that is a date field for example 1/6/2007 12:00 PM and I have another field that is just a time field 3:00 PM. Can I add the time field to the date field to get 1/6/2007 3:00 PM?
Thanks

 

[lbass]

Try:

datetime({table.date},{table.time})

-LB

 

[jwinsten]

This is what I tried but it didn't work
datetime(date({table.date}),time({table.time}))

It only returned records that the time is 12:00 PM. So it was unable to add the records. The rest of the records were coming back false

 

[jwinsten]

Actualy I think the error is coming because it does not recoginze the time as a time. You helped me before to convert the number to a time.

http://www.tek-tips.com/viewthread.cfm?qid=1327220&page=1

Now it will not take the time

 

[lbass]

Please show the real content of your formula, and the contents of any nested formulas, and also identify their datatypes.

-LB

 

[jwinsten]

ShiftDate is a date field
starttime is a number field
endtime is a number field.

So I started to use the formula to convert the number into time. which worked.
numbervar x := tonumber({table.starttime});
time(truncate(x),round(remainder(x,truncate(x))*60),0)
Than I tried to add the formula to the date field and it didn't work only worked when I had a real time field.

datetime(date({table.Shiftdate}),{table.starttime})

Maybe I'm going about this the wrong way becasue what I'm going to need in the long run is for it to check by each employee on a day to make sure they don't have to start and end times in the database for a time period.

If you need futher explaintion please let me know.

 

[lbass]

I think you should be able to use:

//{@time}:
numbervar x := tonumber({table.starttime});
time(truncate(x),round(remainder(x,truncate(x))*60),0)

datetime({table.shiftdate},{@time})

If this doesnt' work, what error message are you getting?

The issue of whether there are multiple starts in the database is some other issue where we'd need to see some sample data along with an explanation of what you're trying to do.

-LB