DERIVING DATE ,YEAR AND MONTH FIELDS FROM A DATE

[Anonymous]

Hi Everyone,

i am trying to derive the date ,month and year fields from a date but the data in tat date field is incosnistent i am using the date# and date functions to manipulate the data but its not working .

syntax i am using :date(Date#(DateOfBusiness,'MM/DD/YYYY')) as Date,

Attached is the date i am using .please do help

kush141087‌gwassenaar‌rwunderlich‌maxgro‌stalwar1‌cleveranjos‌

[maxgro]

If I add in your .qvw a listbox with

Timestamp( Alt(Timestamp(Timestamp#(DateOfBusiness, 'YYYY-MM-DD hh:mm:ss')), DateOfBusiness) )

it seems to work,

so try to replace in your script

timestamp(alt(DateOfBusiness,Timestamp#(DateOfBusiness,'YYYY-MM-DD hh:mm:ss')),'MM/DD/YYYY hh:mm:ss TT') as DateNew

with

Timestamp( Alt(Timestamp(Timestamp#(DateOfBusiness, 'YYYY-MM-DD hh:mm:ss')), DateOfBusiness) ) as DateNew

[Clever_Anjos]

Well, my scripts runs with your excel file

[Anonymous]

but at the last of the list box u can find nulls or just take a year or month list box where u can find nulls

[Clever_Anjos]

You didn´t use my solution into your qvw.

Could you please give a try?

[Anonymous]

i have used your solution but there are some numbers and other data in fields of month and year

attached is the qvw for reference

[effinty2112]

Hello,

Try

LOAD

Month(Date) as Month,

Year(Date) as Year,

Date

;

LOAD

Date(Alt(

Floor(Timestamp#(@1,'YYYY-MM-DD hh:mm:ss')),

Floor(Timestamp#(@1,'DD/MM/YYYY hh:mm:ss'))

))

as Date

FROM

commdate.xls

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

Cheers

[Clever_Anjos]

Please slightly change your code to this, have to check this DateOfBusiness field.

LOAD 

  tfield, 

  Date(Floor(tfield)) as Date, 

  Month(Floor(tfield)) as Month, 

  Year(Floor(tfield)) as Year,

  DateOfBusiness; 

LOAD UniqueID,

      DateOfBusiness,

      Timestamp( Alt(Timestamp(Timestamp#(DateOfBusiness, 'YYYY-MM-DD hh:mm:ss')), DateOfBusiness) ) as DateNew,

      alt( 

  TimeStamp#(DateOfBusiness,'YYYY-MM-DD hh:mm:ss'), 

  TimeStamp(DateOfBusiness), 

  ) as tfield  ,

     FKStoreID as StoreId,

     FKEmployeeNumber,