Creating a Date from a number

[rsimson1]

Hi, I'm needing to create a date from (startdate) in the below script . The format of the (startdate) data coming out the database is pictured below. Can anyone help? I have put in the load script that I'm currently using to pull the start data as pictured.

COURSE:

LOAD

       Coursecounter,

       id as [Course ID],

       category,

       sortorder,

       fullname,

       shortname,

       idnumber as [Course ID Number],

       summary,

       summaryformat,

       format,

       showgrades,

       newsitems,

      startdate,

       marker,

       maxbytes,

       legacyfiles,

       showreports,

       visible,

       visibleold,

       groupmode,

       groupmodeforce,

       defaultgroupingid,

       requested,

       enablecompletion,

       completionnotify,

       cacherev

       calendartype

FROM 10_MOODLE\Raw\ACMoodleCourse.QVD (QVD);

STORE COURSE INTO 10_MOODLE\Optimised\ACMoodleCourse.QVD (QVD);

[swuehl]

I think your date field comes in Unix timestamp format (but I could be wrong):

LOAD

      DayName(MakeDate(1970, 1, 1) + (startdate / 86400)) as NewStartDate,

      startdate,

     ...

[Anonymous]

Hi,

you can try:

COURSE:

LOAD

       Coursecounter,

       id as [Course ID],

       category,

       sortorder,

       fullname,

       shortname,

       idnumber as [Course ID Number],

       summary,

       summaryformat,

       format,

       showgrades,

       newsitems,

     Date(startdate, 'YYYY-MM-DD') as StartDate,

      startdate,

       marker,

       maxbytes,

       legacyfiles,

       showreports,

       visible,

       visibleold,

       groupmode,

       groupmodeforce,

       defaultgroupingid,

       requested,

       enablecompletion,

       completionnotify,

       cacherev

       calendartype

FROM 10_MOODLE\Raw\ACMoodleCourse.QVD (QVD);

STORE COURSE INTO 10_MOODLE\Optimised\ACMoodleCourse.QVD (QVD);