Strange goings on in SQL land
Weird things with dates
I have a simple sql script that extracts raw data from a quality centre database and extracts it to an excel spreadsheet, part of this is shown below:
SELECT bg_bug_id as DefectID,
bg_detection_date as RawDetectionDate,
convert(varchar(10), bg_detection_date, 103) as FormatDetDate,
bg_closing_date as RawClosingDate,
convert(varchar(10), bg_closing_date, 103) as FormatCloseDate
FROM BUG
The issue I'm having is with the formatted dates. Some come through correctly and are left justified with the correct date, the others come through in US format and are right justified. When looking at the excel cell format, the left justified correct dates are showing as General format, and the incorrect right justified dates are Date format.
Yes, I could just do the formatting on the RawDate in Excel which guarantees the correct date, and yes I could do some fancy malarky casting to a string and then back to a date but that would affect performance, so it's solvable, but why is it doing this in the first place? It's bugging the hell out me.
I've tried various things so far, including casting, converting, floats etc... and the most reliable so far is:
CONVERT (varchar(11), convert (DATETIME, BUG.BG_DETECTION_DATE, 103))
However, this is returning the dates in the format 'Mar 23 2010' which isn't really what I want but will do for now.
So what's going on? Any ideas?
I have a simple sql script that extracts raw data from a quality centre database and extracts it to an excel spreadsheet, part of this is shown below:
SELECT bg_bug_id as DefectID,
bg_detection_date as RawDetectionDate,
convert(varchar(10), bg_detection_date, 103) as FormatDetDate,
bg_closing_date as RawClosingDate,
convert(varchar(10), bg_closing_date, 103) as FormatCloseDate
FROM BUG
The issue I'm having is with the formatted dates. Some come through correctly and are left justified with the correct date, the others come through in US format and are right justified. When looking at the excel cell format, the left justified correct dates are showing as General format, and the incorrect right justified dates are Date format.
Yes, I could just do the formatting on the RawDate in Excel which guarantees the correct date, and yes I could do some fancy malarky casting to a string and then back to a date but that would affect performance, so it's solvable, but why is it doing this in the first place? It's bugging the hell out me.
I've tried various things so far, including casting, converting, floats etc... and the most reliable so far is:
CONVERT (varchar(11), convert (DATETIME, BUG.BG_DETECTION_DATE, 103))
However, this is returning the dates in the format 'Mar 23 2010' which isn't really what I want but will do for now.
So what's going on? Any ideas?
Comments