Default date column not aligning in xcel

Database
uco
Enthusiast

Default date column not aligning in xcel

Hi 

I have a date column in which the default low date is not  not aligning with other dates in the columns 

here is the screen shot . 




LAST PMT DATE
10/31/2009
8/2/2005
9/18/2009
12/26/2007
12/13/2006
3/15/2012
4/21/2009
11/9/2011
7/19/2010
10/15/2007
2/3/2012
2/14/2009
11/22/2006
11/17/1858
11/29/2011
7/11/2011
6/23/2009
9/12/2006
5/24/2007
4/28/2006
2/10/2009

 I have the code in bteq script which creates the csv file 

CASE WHEN CAD2.LAST_PMT_DATE =DATE '1858-11-17'  THEN   CAST( '11/17/1858' AS VARCHAR(10))   ELSE (     CAST(CAST (CAD2.LAST_PMT_DATE  AS  FORMAT  'MM/DD/YYYY' )      AS VARCHAR(10))   ) END

I also tried the (     CAST(CAST (CAD2.LAST_PMT_DATE  AS  FORMAT  'MM/DD/YYYY' )      AS VARCHAR(10))

The source format is YYYY-MM-DD , Any reason why only the low date is not aligning?

Thanks

uco

Tags (1)
12 REPLIES
uco
Enthusiast

Re: Default date column not aligning in xcel

the data is not apperaring in the post as I intended,

          10/31/2009

           8/2/2005

            9/18/2009

           12/26/2007

            12/13/2006

             3/15/2012

             4/21/2009

             11/9/2011

11/17/1858

 

The low date 11/17/1858 is not aligning properly along with other dates

Enthusiast

Re: Default date column not aligning in xcel

Hi Uco,

What I have understand is that you have a date format in source YYYY-MM-DD and you want this to export as 'DD/MM/YYYY' 

Please confirm, and also confirm that the sample values you have pasted here are from the exported file?

Khurram
uco
Enthusiast

Re: Default date column not aligning in xcel

Hi Khurram

Yes the date format in source is YYYY-MM-DD and I want to export as DD/MM/YYYY,

Yes sample values are from exported file

Thanks

uco

Enthusiast

Re: Default date column not aligning in xcel

By the way, what is the need to use this case statement? as You have all the dates in CAD2.LAST_PMT_DATE and want to apply the same format. You can simply apply the format to whole column without any case statement.

make sense? Or you want to achieve somthing else?

Khurram
uco
Enthusiast

Re: Default date column not aligning in xcel

Hi Kurram

The reason why I used CASE is the low date 1858-11-17 is not formatting to  'DD/MM/YYYY' , I think I mentioned it in the post

thanks

uco

Junior Contributor

Re: Default date column not aligning in xcel

This is probably because Excel can't handle dates before 1900, regardless of how it's formatted. You can only use that date as a string.

Enthusiast

Re: Default date column not aligning in xcel

Excel doesn't support date before 1900 and considering this value as text value.

See for further details : http://exceluser.com/formulas/earlydates.htm

uco
Enthusiast

Re: Default date column not aligning in xcel

That may be true, but I am converting it to varchar before exporting , it is not a date anymore right?

thanks for the help

uco

Enthusiast

Re: Default date column not aligning in xcel

Hi,

Did you use a trim function? Maybe it will help. Else in excel, you can write a formula or program for those dates where you see they are not proper.

Cheers,

Raja