-
Re: Date Format Problems
Celambarasan Adhimulam Mar 1, 2012 7:26 AM (in response to Anuradha Abeysuriya )Hi,
try with this
Date(Date#(DateField,'YYMMDD'),'YYYY-MM-DD') As Date
Celambarasan
-
Date Format Problems
Anuradha Abeysuriya Mar 1, 2012 6:54 AM (in response to Celambarasan Adhimulam )Thanks For your Reply,
But it doesn't works.
This is the part of the Script i have used
LOAD DESTINATION_CITY,
ORIGIN_CITY,
SHIP_DATE,
Date(Date#("SHIP_DATE",'YYMMDD'),'YYYY-MM-DD')) As "SHIP_DATE"
here it gives the error "Syntax error, missing/misplaced "
Thanks
-
Re: Date Format Problems
jagan mohan rao appala Mar 1, 2012 7:01 AM (in response to Anuradha Abeysuriya )Hi,
Remove quotes for SHIP DATE in Date#().
LOAD DESTINATION_CITY,
ORIGIN_CITY,
SHIP_DATE,
Date(Date#(SHIP_DATE,'YYMMDD'),'YYYY-MM-DD')) As SHIP_DATE
Remove quotes for SHIP DATE in Date#().
Hope this helps you.
Regards,
Jagan.
-
Date Format Problems
Anuradha Abeysuriya Mar 1, 2012 7:07 AM (in response to jagan mohan rao appala )Still no luck,
this is the complete script i'm trying. i'm tring to load data from QVD file and that file only has the shipdate in varchar format.
aaa:
LOAD DESTINATION_CITY,
ORIGIN_CITY,
SHIP_DATE,
Date(Date#(SHIP_DATE,'YYMMDD'),'YYYY-MM-DD')) As "SHIP_DATE" - //I tried both "SHIP_DATE" n SHIP_DATE
FROM
meni1.qvd (qvd) ;
Thanks
-
Re: Date Format Problems
Celambarasan Adhimulam Mar 1, 2012 7:11 AM (in response to Anuradha Abeysuriya )Hi,
Try with this without including Ship_Date twice.
aaa:
LOAD DESTINATION_CITY,
ORIGIN_CITY,
Date(Date#(SHIP_DATE,'YYMMDD'),'YYYY-MM-DD')) As SHIP_DATE
FROM
meni1.qvd (qvd) ;
Celambarasan
-
Date Format Problems
Anuradha Abeysuriya Mar 1, 2012 7:15 AM (in response to Celambarasan Adhimulam )Not works.
-
Re: Date Format Problems
Celambarasan Adhimulam Mar 1, 2012 7:27 AM (in response to Anuradha Abeysuriya )Hi,
Using Double closed paranthesis causing a problem in the date function.
aaa:
LOAD DESTINATION_CITY,
ORIGIN_CITY,
Date(Date#(SHIP_DATE,'YYMMDD'),'YYYY-MM-DD') As SHIP_DATE
FROM
meni1.qvd (qvd) ;
Sorry its my typing mistake.
Check with this now.
Celambarasan
-
Re: Date Format Problems
Anuradha Abeysuriya Mar 1, 2012 7:30 AM (in response to Celambarasan Adhimulam )Great.
Thanks Celambarasan
It works but date is invalid.
my shipdate in the database = 110516 (varchar) and i need to convert it to '2011-05-16' in date format.
Thanks
-
Re: Date Format Problems
Celambarasan Adhimulam Mar 1, 2012 7:33 AM (in response to Anuradha Abeysuriya )Hi,
Yes it will give you that date format only.Which format it returns now?
Celambarasan
-
-
-
Date Format Problems
jagan mohan rao appala Mar 1, 2012 7:29 AM (in response to Anuradha Abeysuriya )Hi,
Can you try this, earlier it has an extra bracket
LOAD
DESTINATION_CITY,
ORIGIN_CITY,
SHIP_DATE,
Date(Date#(SHIP_DATE,'YYMMDD'), 'YYYY-MM-DD') As [SHIPDATE]
FROM
meni1.qvd (qvd) ;
Hope this helps you.
Regards,
Jagan.
-
Date Format Problems
Deepak Kurup Mar 1, 2012 7:33 AM (in response to Anuradha Abeysuriya )HI Anuradha,
A small query. Since your data is in YYMMDD format eg 100202 for 2nd Feb 2010. but how will the system understand
10(YY) stands for 2010.
Do you have data only from 2000 ???? if yes the you can try out the below code.
Date(makedate('20'&left(SHIP_DATE,2),mid(SHIP_DATE,3,2), right(SHIP_DATE,2)),'YYYY-MM-DD') as SHIP_DATE
Deepak
-
Re: Date Format Problems
Anuradha Abeysuriya Mar 1, 2012 7:47 AM (in response to Deepak Kurup )Thanks Deepak,
I have data only from 2000 and you answer helps me lot
Thanks
-
-
-
-
-
-
-