DateTime field is created with timestamp 00:00:00

[#1]

Hi,

We want to extract Year, Month, Date etc. from an existing field (CreatedDateTime). To do this I found the script below that we are using.

The new table is created just as expected. The problem however, is that the Primary Key Field (CreatedDateTime) is created with the correct date but with timestamp 00:00:00 for every record.

The result is that match between records in the two tables does not exist and the association does not work. The question simply is; How do we get the correct time populated from the field CreatedDateTime?

Regards,

Mikael

Date:

load

today()-recno() +1 as Date

autogenerate(Today()-'08-12-31 23:59:59');

CreateDates:

load

Date(Date) as CreatedDateTime,

Day(Date) as CreatedDay,

Year(Date) as CreatedYear,

Month(Date)&' - '&right(year(Date),2) as CreatedMonthYear,

Month(Date) as CreatedMonth,

Weekday(Date) as CreatedWeekDay,

resident Date;

drop table Date;

[hic · Accepted solution]

Use a preceding LOAD. If you put the following string directly before the SQL SELECT, then the result of the SELECT will be piped into the LOAD and your field will be created:

Load *, Date(Floor(CreatedDateTime)) as CreatedDate ;

/HIC

[#3]

Alright! Now it works just fine. Many thanks for the great assistance.

/Mikael

[#4]

Unfortunately I was a little bit too quick. It seems like the field CreatedDate that is created in the new table does not pick the values from the same field in the original table.

The first record is created with the correct date but then the proceeding records are added with one day for each record. If I run the script today it will create the first record as date March 27, the second March 28 and so on.

At the moment the new table is created like this:

Let vStartDate = num(Date#('08-12-31','YY-MM-DD')-1);
Let vToday     = num(Today());

Date:
load
Date($(vToday)+recno()) as Date
autogenerate($(vToday)-$(vStartDate));

OpenDates:
load
Date(Date) as OpenDate,
Day(Date) as CreatedDay,
Year(Date) as CreatedYear,
Month(Date)&' - '&right(year(Date),2) as CreatedMonthYear,
Month(Date) as CreatedMonth,
Weekday(Date) as CreatedWeekDay

resident Date;
drop table Date;

[hic]

If you want only the dates that exist in the Incident table, then you should define your calendar table using a Load resident. If you want your Date table to contain all dates, then you should use the above suggested solution with autogenerate. Any of the two will work fine. See attachment.

/HIC