Dating for DBAs - a second date

Question

Dating for DBAs - a second date

Duncan Pryde

SSCertifiable

Points: 7956
More actions
April 12, 2011 at 10:23 pm

#234694

Comments posted to this topic are about the item Dating for DBAs - a second date
April 13, 2011 at 12:47 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 58 total)

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

All 4 options work? one of those questions again...pick 2 out of 4 correct answers...a quessing game again.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

All 4 options do work under some dateformat settings (but not all dateformat settings).

Only two options are correct as the question is stated.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

This is a good question - thanks Duncan

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

itsmatta SSC Enthusiast Points: 117 More actions · Answer 4

All 4 works correctly and I have selected only 2 as per the question.. So I am right and need my point back

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 5

Thank-you, Duncan for this wonderful series!

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

itsmatta (4/12/2011)
All 4 works correctly and I have selected only 2 as per the question.. So I am right and need my point back

Did you change your dateformat at all?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 7

itsmatta (4/12/2011)
All 4 works correctly and I have selected only 2 as per the question.. So I am right and need my point back

No, all 4 do not work correctly everywhere (i.e. irrespective of date format and/or language settings). DATETIME and SMALLDATETIME are affected by the format.

Per Books On Line http://msdn.microsoft.com/en-us/library/ms189491.aspx,

The effect of the DATEFORMAT setting on the interpretation of character strings might be different for datetime and smalldatetime values than for date, datetime2 and datetimeoffset values, depending on the string format.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 8

Henrico Bekker (4/12/2011)
All 4 options work? one of those questions again...pick 2 out of 4 correct answers...a quessing game again.

Try this:

SET DATEFORMAT ydm

GO

SELECT CAST('2011-04-28' AS DATETIME)

or this:

SET LANGUAGE British

GO

SELECT CAST('2011-04-28' AS DATETIME)

and do the same for the other date types, and you'll see that DATETIME and SMALLDATETIME produce an error while DATE and DATETIME2 don't.

So no guessing required!

Duncan

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 9

Nakul Vachhrajani (4/13/2011)
Thank-you, Duncan for this wonderful series!

You're welcome - although I'd hesitate to call 2 questions a series! Unless you're expecting more? :blink:

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 10

CirquedeSQLeil (4/12/2011)
This is a good question - thanks Duncan

Thanks for the compliment Jason - glad you liked it.

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 11

Duncan Pryde (4/13/2011)
Henrico Bekker (4/12/2011)
All 4 options work? one of those questions again...pick 2 out of 4 correct answers...a quessing game again.
Try this:
SET DATEFORMAT ydm
GO
SELECT CAST('2011-04-28' AS DATETIME)
or this:
SET LANGUAGE British
GO
SELECT CAST('2011-04-28' AS DATETIME)
and do the same for the other date types, and you'll see that DATETIME and SMALLDATETIME produce an error while DATE and DATETIME2 don't.
So no guessing required!
Duncan

question doesnt state that you need to SET LANGUAGE....

anyway.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 12

Henrico Bekker (4/13/2011)
question doesnt state that you need to SET LANGUAGE....
anyway.

It says:

"regardless of any date format or language settings".

You change language settings using SET LANGUAGE and date format settings using SET DATEFORMAT.

Duncan

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 13

Duncan Pryde (4/13/2011)
Nakul Vachhrajani (4/13/2011)
Thank-you, Duncan for this wonderful series!
You're welcome - although I'd hesitate to call 2 questions a series! Unless you're expecting more? :blink:

Definitely expecting more 🙂 Maybe I like them because a few years ago, we made major changes to our product to support globalization/internationalization and came across all these issues then. These QotDs are great learning opportunities for anyone who would want to work on a product that would work no matter where you take it.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins