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|
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?
the data is not apperaring in the post as I intended,
The low date 11/17/1858 is not aligning properly along with other dates
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?
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
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?
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
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.
Excel doesn't support date before 1900 and considering this value as text value.
See for further details : http://exceluser.com/formulas/earlydates.htm
That may be true, but I am converting it to varchar before exporting , it is not a date anymore right?
thanks for the help
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.