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?

Comments

Post a Comment

Popular posts from this blog

Yay - It works! Rebuild/Reorganize all indexes in one go - woo hoo

-
/*Version 1 of my script to fix all fragged indexes on a database */ /*Must be logged in as [sa] for this to work, if not, you will get permissions errors*/ /*You should be able to copy and paste this script in its entirity to your SQL 2005 or higher query window and run it without altering anything other than the USE .*/ /*Perform a 'USE ' to select the database in which to run the script.*/ USE AdventureWorks GO -- Make sure temp table doesn't exist IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmp_fraglist]') AND type in (N'U')) DROP TABLE [dbo].[tmp_fraglist] GO -- Declare variables SET NOCOUNT ON DECLARE @tablename NVARCHAR (255) DECLARE @indexname NVARCHAR (255) DECLARE @indextype NVARCHAR (255) DECLARE @execstr NVARCHAR (255) DECLARE @pagecounts INT DECLARE @frag DECIMAL DECLARE @reorgFragSize DECIMAL DECLARE @rebldFragSize DECIMAL DECLARE @numrecords INT DECLARE @rowcount INT DECLARE @errorMessage
Read more

What is a geek?

-
Someone raised an interesting point on Monday night at a GGD dinner and I feel they have missed the entire point of Girl Geek Dinners and have also, with their comments, seriously undermined other women who are looking to come out of their shell and interact with other 'geeks'. If you don't agree with what we're doing then don't come to a Girl GEEK dinner, and certainly don't turn up and throw cold water on people, make belittling comments and then moan that it was too technical and you didn't enjoy it. So what is a geek? There seemed to be some confusion. These women throwing around the derogatory comments seem especially confused. So let's refresh everyones' minds. You don't need to be in IT to be a geek folks! It's not an exclusive club limited to women who don't baulk at the thought of writing code. We're a diverse crowd and we celebrate each others geekiness, even if it isn't something we're into. We certainly
Read more

The Grace Jones

-
Well, we finished recording the CD and now all the hard work is starting, the editing, mixing, production etc... We've also got some guys working on a video for us which sounds as though it's going to be interesting. Time to sell the house in order to pay for the tour of Ireland next year I guess. :)
Read more