pickup the data

[dhanu_today]

Dear All,

I have a field with the following sample values:

0

3

7

10

201305

7

4

178

201307

201408

201305

200209

201305

We need the data which contains the YYYYMM(format).It means that we have to capture value which contains 6 digits.

Second one is we have to capture only particular data like 201305 only.

Thanks,

Dhanu

[swuehl]

Maybe like

LOAD Field FROM YourTable WHERE Len(Field) = 6;

I am not sure what you mean with particular data, can you detail this requirement?

[dhanu_today]

Thanks Swuehl,

As part of my req 1. we have to capture only 6 digit number from that filed then convert that into number into date format. like 201408(YYYYMM)

2.We have to capture only particular 6 digit numbers like 201305(It repeats more times).

Consider the ID field contains some numbers like 0,4,178 and some date formated data like 201305.

O/p :1

ID               SAL

201305        9000

201307      10000

201408      120000

201305       140000

200209        150000

201305        130000 

Thanks,

Dhanu

[swuehl]

To convert the 6 digit number to a date (i.e. a monthstart date), you can use interpretation functions like date#():

LOAD

    Date#(Field,'YYYYMM') as DateField

FROM YourTable WHERE Len(Field) = 6;

I still understand what you mean with capture particular numbers like 201305, you don't want to get all the other monthstart dates?

[dhanu_today]

Yes your right...I dont want all other monthstart dates.

Thanks,

Dhanu

[dhanu_today]

I have applied the below two conditions.

where Len(ID)=6 and ID='201305';

Thanks,

Dhanu

[Clever_Anjos]

You don´t need to check twice

where ID='201305'; is enough