Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I'm new to QlikView.
I have got my data in MS SQL database.
The DateX column is stored as the number of days since 28/12/1800.
To questions:
1. How to get to the proper date value in QlikView for a number 77070 (I know it should be 01/01/2012.
2. Is it possible to use in QlikView script MS SQL Scalar-valued function which does the conversion to proper date. (Function takes the DateX column as an argument and returns varchar(10) string).
Thanks in advance for your contribution.
Regards
Adam
HI,
Try like this,
=Date(77070,'DD/MM/YYYY')
Hope it helps
Hi Mayil
When I have used it in QV script as SQL Select element I have got an error:
ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'Date' is not a recognized built-in function name.
More ideas?
Regards
Adam
HI,
Load *, Date(FieldName,'DD/MM/YYYY') as DateField;
Select * from TableName;
Hope it helps
No sccess.
Instead giving me date 01/06/2012 it gives me 04/06/2111.
That means Date functions works somehow, but not as I expect.
Regards
Adam
I think there should be something more elegant to get the correct date from your MS SQL server, but if you only get a number back, you need to subtract the difference in days of the calendar origins (30.12.1899 - 28.12.1800), which is
36161.
So, try subtracting this number from your date field:
=date(77070-36161)
resp
LOAD
Date(DATEFIELD - 36161) as Date,
...
FROM ...;
Hi Swuehl!
And your elegant way works great.
Thanks a lot.
But I'm still looking for an answer for the second question:
Is it possible to use MS SQL Scalar-valued function in QlikView script? If yes, how to do it?
Regards
Adam