How do I add an existing date field to a time field to make a new date time field (for a correspondence file import)? This seems like it ought to be easy, but it's not obvious to me. I suppose one could use the addDate and format functions 3 times for h+m+s.
I'm not sure where you are trying to add a field.
The Date/Time field type allows you to show only date or only time data, but the other piece of the Date/Time data is still there. If you are trying to combine a Date?time field that shows only date with onle that shows only time, and populate the results back into another Date/Time field, you will probably need a gINT Rule to do that
On the other hand, if you are trying to report the date and time from two fields that show only date/only time, that is a simpler matter.
Let's say the table TABLE has two such Date/Time fields: Date Only and Time Only. One Text Expression to output Date followed by Time might be:
Format(<<TABLE.Date Only>>,D MMM YYYY) Format(<<TABLE.Time Only>>,H:MM:SS)
I want to import water data from file with split date and time fields into a file with a combined date and time field. I thought it was the sort of thing that a correspondence file could do, but the statement doesn't seem to be as simple as I expected. The above format statement is the sort of thing that I would expect to work, but it doesn't. Is there a string to date function that's necessary?
More information please.
Please indicate the format the existing data is in. Specifically, is the existing date and time stored as text (example "2/13/2012" and "12:15 PM" or is it stored as separate date numbers (an integer greater than 0 representing the number of days since 1980) and time numbers (a decimal less than 1 representing the fractional number of days elapsed since midnight) Example 40961 for the date and 0.4567 for the time.
Also verify what you mean by combine. Do you want two text strings concatenated together to form one long text string that will be stored in a text field in gINT or do you want a numeric date/time value that will be stored in a date/time field in gINT.
Finally (though it doesn' really matter), could you indicate the source file type. ie is it a coma delimited text file, and excel spreadsheet, an acess database, or another gINT project file?
It's going from one gpj database to another, from two datetime fields to one datetime field. So the final result needs to be in gINT's datetime format. I started by using the calc function to sum them like I would in excel, but that didn't work. Pasting them end to end seems seems to give a text result that gINT doesn't import.
Note that sometimes the time field will be empty, so the date should be able to import by itself, too. (I'm fairly sure that I can manage this part of the process myself.)
I do not know why simply adding the date and the time does not work. Everything Iknow about access says that it should. They should both be stored as decimal numbers of days. I have found some weirdness in the way gINT handles dates and times though so who knows.
As a work around I sugest placing the following in your correspondense file:
<<DateAdd(S,<<Calc(<<Format(<<POINT.Time>>,H)>>*3600+<<Format(<<POINT.Time>>,N)>>*60+<<Format(<<POINT.Time>>,S)>>)>>,<<POINT.Date>>)>>
Where <<POINT.Date>> and <<POINT.Time>> are the two fields that you want to combine into one date/time field. I have tried it out and it seems to work fine. It simply computes the number of seconds represented by the time only entry and then uses the DateAdd function to add that number of seconds to the date only entry.
You could add some conditional stements to this using the isdateonly and istimeonly functions to handle cases when the original entries do contain both a date and time.