SQL Teaser (string to date conversion) 1

[gmmastros]

Without running this, what do you think the output is?

[COLOR=blue]Set[/color] [COLOR=#FF00FF]DATEFORMAT[/color] DMY
[COLOR=blue]Select[/color] [COLOR=#FF00FF]Convert[/color]([COLOR=#FF00FF]DateTime[/color], [COLOR=red]'100205'[/color])

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[onpnt]

I was wrong just for the books

_{____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own}

 

[monksnake]

Let me try

Spoiler

02-10-2005 00:00:00.000 ???

<.

 

[gmmastros]

Nope. Try again.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

Spoiler

2005-02-10 00:00:00.000

Last try, then I cheat.

<.

 

[onpnt]

Nope but if it makes you feel better one of your guesses is about the same as mine

_{____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own}

 

[SQLBill]

My guess:

Spoiler

2005-02-10 00:00:00

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Shoot - tested it and I got it wrong. Now I'm waiting for the explanation of why it happens that way.

-SQLBill

Posting advice: FAQ481-4875

 

[fredericofonseca]

The SET DATEFORMAT session setting does not apply to all-numeric date entries such as numeric entries without separators. The 6-digit or 8-digit strings are always interpreted as ymd. The month and day must always be 2 digits.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[ptheriault]

Spoiler

My guess would be an out of range date?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Hmmm,
Well, I was wrong about that one!



- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[nice95gle]

My try

Spoiler

02 oct 2005 12:00AM

Well Done is better than well said
- Ben Franklin

 

[aspvbnetnerd]

My turn

Spoiler

20050210

 

[ca8msm]

I actually guessed correct! Must have been a one off though, I'll try not to do it again.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[gmmastros]

fredericofonseca was completely right. If I had said, "What is the output?" for Select Convert(DateTime, '20100205'), I suspect that most of you would have gotten it correct. Feb 5, 2010.

Since there were only six digits, and I put a Set DATEFORMAT in the code, it threw you all off.

When there are only six digits, SQL Server will use the Cutoff Date for century (50 and above is 1950, 49 and below is 2049).

Star to Frederico for the first to post the correct answer.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[fredericofonseca]

Thanks George.


This one was easy for me as I have dealt with it before.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com