All Forums Database
uco 15 posts Joined 12/13
06 Dec 2013
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
        

uco 15 posts Joined 12/13
06 Dec 2013

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

M.Saeed Khurram 544 posts Joined 09/12
06 Dec 2013

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 15 posts Joined 12/13
06 Dec 2013

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

M.Saeed Khurram 544 posts Joined 09/12
06 Dec 2013

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 15 posts Joined 12/13
06 Dec 2013

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

dnoeth 4628 posts Joined 11/04
06 Dec 2013

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.

Dieter

Kawish_Siddiqui 37 posts Joined 03/07
06 Dec 2013

Excel doesn't support date before 1900 and considering this value as text value.
See for further details : http://exceluser.com/formulas/earlydates.htm

Kawish Siddiqui -

uco 15 posts Joined 12/13
06 Dec 2013

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

Raja_KT 1246 posts Joined 07/09
06 Dec 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
07 Dec 2013

Hi uco,
the sample data is not from a file created by BTEQ, a FORMAT 'DD/MM/YYYY' will never create single digit month/day values.
When you import the data into Excel it will automatically try to convert it to an Excel date which doesn't support dates before 1900. '1858-11-17' will be treated as a string instead and no Excel date format will be applied. You can only export it in the same format you want to use in Excel and then set the column alignment to right aligned in Excel, at least it will look like the other dates.

Dieter

uco 15 posts Joined 12/13
07 Dec 2013

Thanks Dieter for your explanation, thanks everyone . As said by Siddiqui, Dieter it is issue with excel
Dieter , I took the date values from the CSV file created by BTEQ script . 
 
Thanks
uco

Raja_KT 1246 posts Joined 07/09
08 Dec 2013

Excel does have an issue with dates before 1900. However, there is a workaround for it. This script, I test in excel and it works fine with few extra values:

http://support.microsoft.com/?kbid=245104

Anyway, it is a good learning. My bad , I did not read the problem properly at start.

Cheers,

Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.