Literal Dates

Question

Post reply

Literal Dates

Viewing 9 posts - 16 through 23 (of 23 total)

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

Revenant SSC-Forever Points: 42467 More actions · Answer 1

Revenant

SSC-Forever

Points: 42467

July 23, 2012 at 11:14 am

#1516362

Excellent QotD -- thanks!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

The first SET DATEFORMAT is what started to throw me, then I realized that it wouldn't lay a part in the conversion of the date literals when the view was queried.

Very similar to a question back on June 6, 2012. Easier to read the answers though.

Definately needed considering the number of wrong answers at this time (65% Wrong).

ehab.abuzied SSC Rookie Points: 25 More actions · Answer 3

When i run this question on SQL Server 2008 gave me (0,0).

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

ehab.abuzied (7/24/2012)
When i run this question on SQL Server 2008 gave me (0,0).

You may want to double check the codee you ran. I ran it on SQL Server2008 R2 and it returns the correct answer.

TomThomson SSC Guru Points: 104773 More actions · Answer 5

SQLRNNR (7/23/2012)
Seems very similar to another we had recently.

Yes, it's very like a recent one.

Judging by the numbers getting it wrong it still needs airing. So a good question.

Tom

nerosdk71 Valued Member Points: 74 More actions · Answer 6

It is simply not a correct question and answer. the answer MIGHT be what the author of the question says, but it can also be 1,1. It depends on the regional setting as the author writes.

I have now tested on my sql server, and I got 1,1 with this code

set dateformat Ymd

go

select COUNT(*) as firstcount

from testdates where dt1=dt2

set dateformat Ydm

select COUNT(*) as secondcount

from testdates where dt1=dt2

So this is, as I see it, a question with multiple answers.

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 7

sj 74463 (7/29/2012)
It is simply not a correct question and answer. the answer MIGHT be what the author of the question says, but it can also be 1,1. It depends on the regional setting as the author writes.
I have now tested on my sql server, and I got 1,1 with this code
set dateformat Ymd
go
select COUNT(*) as firstcount
from testdates where dt1=dt2
set dateformat Ydm
select COUNT(*) as secondcount
from testdates where dt1=dt2
So this is, as I see it, a question with multiple answers.

Can you post your definition of testdates? Also what results do you get with the script below

IF OBJECT_ID('DtView') IS NOT NULL DROP VIEW DtView;

GO

SET DATEFORMAT YDM

GO

CREATE VIEW DtView(dt1,dt2)

AS

SELECT CAST('2012-04-10' AS DATETIME),CAST('2012-04-10' AS DATE);

GO

SET DATEFORMAT YMD

GO

SELECT COUNT(*) AS FirstCount FROM DtView WHERE dt1=dt2;

GO

SET DATEFORMAT YDM

GO

SELECT COUNT(*) AS SecondCount FROM DtView WHERE dt1=dt2;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Neha05 Default port Points: 1494 More actions · Answer 8

Neha05

Default port

Points: 1494

August 8, 2012 at 11:45 am

#1523292

Good question, learned something today 😀

kapil_kk SSC-Insane Points: 21316 More actions · Answer 9

when i run that query I got answer as 1,1 and in poll its showing 1,0 as correct answer.

I dont know why...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/