Strange behaviour or expected behaviour with convert and left?

Question

Strange behaviour or expected behaviour with convert and left?

Gé Brander

SSCarpal Tunnel

Points: 4353
More actions
August 2, 2005 at 2:24 am

#66785

I get the variable 20050802 back from a table and need to make it a time representation with - in it.
SELECT CONVERT(datetime, (SELECT CONVERT(varchar(50), 20050802)), 101)
Gives back:
------------------------------------------------------
2005-08-02 00:00:00.000
(1 row(s) affected)

SELECT LEFT((SELECT CONVERT(datetime, (SELECT CONVERT(varchar(50), 20050802)), 101)),10)
Gives back:
----------
Aug 2 200
(1 row(s) affected)

So why does SQL Server 2000 gives 'Aug 2 200' back when I do a left 10 from '2005-08-02 00:00:00.00'?
Am I missing a point here? Or is there a simpler solution?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 1

You have a datetime and apply a string function to it - that's probably why. I am sure there is a simpler solution, but I am not sure what you want. My guess is the following:

select substring(convert(varchar, 20050802), 1, 4) + '-' +

substring(convert(varchar, 20050802), 5, 2) + '-' +

substring(convert(varchar, 20050802), 7, 2)

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

Is this what you want?

SELECT LEFT(CONVERT(VARCHAR(19), CAST('20050802' AS DATETIME), 120), 10)

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

Eh, and when my mind got around to finishing that thought (damn those write-ahead threads in my head):

SELECT CONVERT(VARCHAR(10), CAST('20050802' AS DATETIME), 120)

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 4

Jesper-244176

SSCertifiable

Points: 7142

August 2, 2005 at 3:08 am

#578757

Use "edit post" and noone will ever notice

Gé Brander SSCarpal Tunnel Points: 4353 More actions · Answer 5

Thanks, indeed that is what I want. But I still do not understand what goes wrong in my code. Where is the itch?

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 6

My guess:

The first select returns a datetime. '2005-08-02 00:00:00.000' is simply the way the query analyzer displays this date. The second select, however, makes an implicit conversion (before you apply the left function) from datetime to varchar and gets 'Aug 2 2005 12:00AM'.

Gé Brander SSCarpal Tunnel Points: 4353 More actions · Answer 7

Sounds plausible. Any idea how to change the way Query Analyzer displays these things?

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 8

Jesper-244176

SSCertifiable

Points: 7142

August 2, 2005 at 5:39 am

#578792

I have no idea, but maybe someone else...

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 9

Any idea how to change the way Query Analyzer displays these things?

Tools/Options/Connections. Check 'Use regional settings when displaying currency, number, dates and times' to make QA use whatever you have configured for yourself.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

David Burrows SSC Guru Points: 65142 More actions · Answer 10

So why does SQL Server 2000 gives 'Aug 2 200' back when I do a left 10 from '2005-08-02 00:00:00.00'?

Because if you do string manipulation on a datetime (ie LEFT) sql does an implicit conversion using the default style of mon dd yyyy hh:miAM (or PM) which is 11 characters for the date.

Far away is close at hand in the images of elsewhere.
Anon.