Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

sudhakar_budde
Contributor

Julian Date conversion in the script

Hi,

We have Julian dates in our SQL Server database.

I can convert them on the database client as well as in the script at the sql part of the script.

But I am doing two part ETL. 'Source --> Staging' and  'Staging --> Final qvd'.

So, I don't want to perform coversion on the database in the 'Source --> Staging' script.

Once the data is loaded to Staging, can I convert in the 'Staging --> Final qvd' script??

I mean without hitting the database again.

E.g:

  SELECT schedwin.WO.DATECREAT,  DATEADD(s, ABS(DATECREAT), '19700101') AS
Record_Create_Datetime FROM schedwin.WO

Result:

DATECREAT  Record_Create_Datetime
14328090612015-05-28 10:31:01.000
14412913512015-09-03 14:42:31.000
14412919522015-09-03 14:52:32.000

Is there any wahy to do this in QlikView script please?

Thanks

SB

Tags (1)
2 Replies
Employee
Employee

Re: Julian Date conversion in the script

These are not Julian dates. They are UNIX times, i.e. number of seconds since 1970-01-01.

Try

  TimeStamp(MakeDate(1970) + DATECREAT/24/60/60) as MyDate

HIC

sudhakar_budde
Contributor

Re: Julian Date conversion in the script


Hi Henric,

This is working and  is really helpful.

Thank you very much for the quick answer.

Regards

SB

Community Browser