Problem with date format or function

[#1]

Hi  Everyone,

I`m a beginer in QV and I work on Personal edition. I have a problem with Date. Enclosed you will find my source file. Simply I would like to do functions below:

FileBaseName() as Source,
Date(Date#([],'YYYY-MM-DD HH:MM:SS'),'DD-MM-YYYY') as Date,
   Year(Date(Date#([],'YYYY-MM-DD HH:MM:SS'),'DD-MM-YYYY')) as Year,
       Month(Date(Date#([],'YYYY-MM-DD HH:MM:SS'),'DD-MM-YYYY')) as Month,
        Day(Date(Date#([],'YYYY-MM-DD HH:MM:SS'),'DD-MM-YYYY')) as Day,
           'Q' & CEIL(num(Month(Date(Date#([Data wysłania],'YYYY-MM-DD HH:MM:SS'),'DD-MM-YYYY')))/3) as Quarter

but they don't work properly on my source file. Do you have some ideas how to solve this problem?

[Anonymous]

Try like below

Date(left(Date#,10) as Date etc...

[Anonymous]

The format is case sensitive.  Use 'YYYY-MM-DD hh:mm:ss'.  And, looks like you don't need time part.

Next, see if the Date is created correctly:

Date:

LOAD DISTINCT

     Date(floor(Date#([],'YYYY-MM-DD HH:MM:SS'),'DD-MM-YYYY')) as Date

FROM...

If it is, load the rest:

LEFT JOIN (Date) LOAD

     Year(Date) as Year,

     ....

RESIDENT Date;

[#4]

Thanks you for your reply but, unfotunately it still doesn't work I supposed that problem is in the date format, but I can't see any difference in the excel. As you can see in attached picture, after  basic function like this

Date#([@1],'YYYY-MM-DD HH:MM:SS') as @1 my data looks like that. I have tried many combinations, but I didn't solved this problem.

[ashfaq_haseeb]

Hi, may be like this.

Regards

ASHFAQ

[#6]

Hi Ashfaq,

could you send my youre script on txt format? I`ve problem with open qvw files, cause I`ve Personal Edition

Thanks you in advance

Ewelina

[ashfaq_haseeb]

Hi,

Please find below code.

Table:

LOAD @1,

     date(makedate(left(@1,4),mid(@1,6,2),mid(@1,9,2)),'YYYY-MM-DD') as Date

FROM

LB18_20140513_100839.xls

(biff, no labels, table is Sheet1$);

Regards

ASHFAQ

[#8]

Thank you very much   my script (below) is working, but is there possible to save orginal date?

Dates:

LOAD date(makedate(left(@1,4),mid(@1,6,2),mid(@1,9,2)),'YYYY-MM-DD HH:MM:SS') as Date1,

  Date(@1) as Date,

     Year(@1) as Year,

     Month(@1) as Month,

      Day(@1) as Day,

     'Q' & CEIL(num(Month(Date([@1]))/3)) as Quarter

FROM

LB18_20140513_100839.xls

(biff, no labels, table is Sheet1$);

[ashfaq_haseeb · Accepted solution]

Hi,

Below code must work for you.

Main:
LOAD @1 as OriginalDate,
     date(makedate(left(@1,4),mid(@1,6,2),mid(@1,9,2)),'YYYY-MM-DD') as Date
FROM
LB18_20140513_100839.xls
(biff, no labels, table is Sheet1$);

left join(Main)

load Date,

     Year(Date) as Year,
     Month(Date) as Month,
      Day(Date) as Day,
     'Q' & CEIL(num(Month(Date(Date))/3)) as Quarter
Resident Main;

Regards

ASHFAQ

[#10]

Thanks a lot for your help