Convert decimal number to date

[#1]

First of all I am new (since today) and am saying hello to you all!

I have an application that sees the decimal number decimal number 1 as 31 semptember 1971. After that it is incrementing daily with ++. So today is 14209. How do I convert this to normal date notation ( ex. 25-11-2010)

Thank you in advance!

[#2 · Accepted solution]

Hello,

if I got you right, your source system counts the days starting in Sep1971. QV does it in a similar way. Today in QV is Number 40507. You can proof this while creating a text object with

=num(today())

. Without the num()-function today() returns the date in your date-format.

So with this in mind it should be easy for you to add the necessary offset while loading to get the according dates. Don't forget to tell QV to convert the incoming numbers to be a date similar to this

Load ... date(sourcedate + offset) as MyFirstQVDate;

Regards, Roland

[#3]

Thank you for replying this quick. But I don't quite understand.

I have the next in the editor:

LOAD

"sched_offb_dep_date"

as StartDate,

"sched_offb_dep_time"

as StartTime,

"sched_onbl_arr_date"

as EndDate,

"sched_onbl_arr_time"

as EndTime;

SQL

SELECT sched_offb_dep_date, sched_offb_dep_time, sched_onbl_arr_date, sched_onbl_arr_time

FROM

prod.dbo."ac_util_netl_two_hist";

where sched_onbl_arr_time contains the date issue. What do I add so it is not displayed in 14209 but in 25-11-2010.

[#4]

Hi again,

try this:

date("sched_offb_dep_date" + 26298) as StartDate,
"sched_offb_dep_time" as StartTime,
date("sched_onbl_arr_date" +26298) as EndDate,
"sched_onbl_arr_time" as EndTime;
. . . . .

Hint: The offset is 26298 --> (QV today - sourcesystem today) = (40507 -14209) . The date() converts numbers into dates.

RR

[#5]

Thank you for your explanation, It was very clear now.

I got it to work!