Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

load date to get day,month,year

Hi there,

How can I get the Day, Month, Year from the [WO Created], which is in date/time format.

Thanks

LOAD [WO No],

     [Cntr ID],

     [Cntr No],

     [WO Created],

     Terminal,

    

, 

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table);

Thanks

13 Replies
Anil_Babu_Samineni

Use this?

LOAD [WO No],

    [Cntr ID],

    [Cntr No],

    [WO Created],

    Terminal,

    [Code],

    Year([WO Created]) as Year,

    Day([WO Created]) as Day,

    Month([WO Created]) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table);

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Not applicable
Author

Hi

How can I load only certain month data for e.g Jan , Feb , Mar under year 2017 only.

Thanks

Anil_Babu_Samineni

May be this?

LOAD [WO No],

    [Cntr ID],

    [Cntr No],

    [WO Created],

    Terminal,

    [Code],

    Year([WO Created]) as Year,

    Day([WO Created]) as Day,

    Month([WO Created]) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table) where Match(Year,'2017') and Match(Month, 'Jan','Feb','Mar');

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Not applicable
Author

go this error:

Field not found - <Year>

LOAD [WO No],

     [Cntr ID],

     [Cntr No],

     [WO Created],

      [Job description],

    Year([WO Created]) as Year,

    Day([WO Created]) as Day,

    Month([WO Created]) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table)where Match(Year,'2017') and Match(Month, 'Jan','Feb','Mar')

Anil_Babu_Samineni

May be try simple way with Preceding load

LOAD * Where Year = 2017 and Month = WildMatch(Month,'Jan','Feb','Mar');

LOAD [WO No],

    [Cntr ID],

    [Cntr No],

    [WO Created],

    Terminal,

    [Code],

    Year([WO Created]) as Year,

    Day([WO Created]) as Day,

    Month([WO Created]) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table);

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Kushal_Chawda

Data:

LOAD *

Where Year ='2017';

LOAD [WO No],

     [Cntr ID],

     [Cntr No],

     [WO Created],

      [Job description],

   Year(floor([WO Created])) as Year,

    Day(floor([WO Created])) as Day,

    Month(floor([WO Created])) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table);

Anil_Babu_Samineni

Or this

Sample:

LOAD [WO No],

    [Cntr ID],

    [Cntr No],

    [WO Created],

      [Job description],

  Year(floor([WO Created])) as Year,

    Day(floor([WO Created])) as Day,

    Month(floor([WO Created])) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table);

NoConcatenate

LOAD * Resident Sample Where Year = 2017 and Month = WildMatch(Month,'Jan','Feb','Mar');

DROP Table Sample;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
vinieme12
Champion III
Champion III

kokleong_ong  No need to do preceding load

Data:

LOAD [WO No],

     [Cntr ID],

     [Cntr No],

     [WO Created],

      [Job description],

   Year(floor([WO Created])) as Year,

    Day(floor([WO Created])) as Day,

    Month(floor([WO Created])) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table)

Where Year(floor([WO Created])) = 2017 and Month(floor([WO Created])) <=3 ;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

Hi there

I trying to add on the last script to exclude the [Job code] values as listed but seem not working.

LOAD [WO No],

     [Cntr ID],

     [Cntr No],

     [WO Created],

     [Job code],

     [Job description],

    Year([WO Created]) as Year,

    Day([WO Created]) as Day,

    Month([WO Created]) as Month

FROM

(ooxml, embedded labels, table is WO_with_Job_codes_table);

NoConcatenate

LOAD * Resident WO_with_Job_codes_table Where Year = 2017 and Month = WildMatch(Month,'Jan','Feb','Mar');

LOAD * Resident WO_with_Job_codes_table Where [Job code] <> WildMatch([Job code],'1104','1106','1109','1136','1750','1801','1802','1803','1824','1825','2001','2002','2003','2004','2006','2007','2010','2012','2013','2014','2015','2017','2018','2019','2020');

DROP Table WO_with_Job_codes_table;