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;

[CELAMBARASAN]

Hi,

     Then Convert it to timestamp instead of date

CreateDates:

load

TimeStamp(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;

Or Make change in the another table by formatting the CreateDateTime field to Date instead of timestamp.

Celambarasan

[#3]

mickeandersson wrote:

Date(Date) as CreatedDateTime,

Date() by default have format included only DD MM and YYYY, you need to use Timestamp().

if (MyEnglish == bad) {

     Sorry;

}

[#4]

Hi,

I changed the script to Timestamp() instead of Date() but still get the same result. The time is set to 00:00:00 in every record.

/Mikael

[hic]

If you want to omit the fractional part of the CreatedDateTime, then you should use

Date( Floor(CreatedDateTime) )

This will round the date to the nearest integer below and then format the number as a date. Then the associations should work.

/HIC

[#6]

Thanks, Henric.

Maybe there is a lack of knowledge here but exactly where in the script should I use this string?

/Mikael

[hic]

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

Let vToday     = num(Today());

Dates:

load

Date(Date) as CreatedDate,

Day(Date) as CreatedDay,

Year(Date) as CreatedYear,

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

Month(Date) as CreatedMonth,

Weekday(Date) as CreatedWeekDay;

load

Date($(vToday)+recno()) as Date

autogenerate($(vToday)-$(vStartDate));

TransactionTable:

Load

...

Date( Floor(CreatedDateTime) ) as CreatedDate

from TransactionTable;

[#8]

Hi Henric,

Still a little bit confused. I guess the TransactionTable is the table where CreatedDateTime is originally located and that I should create a CreateDate field in this table to match the one created in the "Dates" -table. That´s a good idea I think.

I did some testing but still it doesn´t work properly. I´m not sure what you mean by "..." above an not "from TransactionTable" either. The original table is called Incident. I simply added the string below into the script and guessed that a CreateDate -field would be created similar to the one in the "Dates" -table.

Incident:

Load

Date(Floor(CreatedDateTime)) as CreatedDate

I did not receive any errors when loading the script. However, nothing is created in the Incident -table. Am I thinking or doing something wrong?

Regards,

Mikael

[hic]

OK, your main/fact/transaction table is called Incident. Then you should create a field CreatedDate in your Dates table (as I think you already have) and a corresponding field in your Incident table:

Date(Floor(CreatedDateTime)) as CreatedDate

I do not know how your LOAD/SELECT statement for the Incident table looks, but make sure that one of the fields created is the CreatedDate. The "..." in my previous answer just denotes the other fields in your Incident table.

When you have run the script, hit <ctrl>-T to check that the data structure is the way you want it. There should be a link between the two tables.

/HIC

[#10]

Ok, that confirms that I think the same way as you do, Henric. The select statement looks like below. My problem is that I don´t know the syntax to combine SELECT from the external data source and LOAD of the new field CreateDate.

I will search in the community today as well. If you have any suggestions, that would be great.

Regards,

Mikael

SQL SELECT Category1ID,

    Category2ID,

    Category3ID,

    ClosedBy,

    ClosedDateTime,

    CreatedBy,

    CreatedDateTime,

    CustomerReference,

    Description,

    IncidentID,

    IncidentNumber,

    MailBoxID,

    ModifiedBy,

    ...,       

FROM "SupportPoint_SE".dbo.Incident;