November 8, 2010 at 10:09 am
timothy bates (11/8/2010)
Set Language 'GREECE'--The format for dates used here is YYYYMMDD and is intended to be functional within your environment.
CREATE TABLE [dbo].[Gregory] (
[dt] [datetime] NOT NULL ,
[row] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO Gregory VALUES ('19160821',1)
INSERT INTO Gregory VALUES ('19160921',1)
INSERT INTO Gregory VALUES ('19161021',1)
Select count * from Gregory
Should I get an error or count 2 instead of 3? :hehe:
You should get 2 errors: the language should be Greek, not Greece, and the count * should be count(*) or even better count(1). Other than that, when you opt to use YYYYMMDD format, it is independent on local settings, as it is one of the 2 universally accepted ISO formats, meaning that once your syntax is corrected, you should get 3 records and no errors 🙂
As far as switching to the Gregorian from Julian, while Greece accepted to change their calendar, Orthodox Christian Church did not, and therefore, all Holidays are still off by 13 days. For example, in Greece, Russia, Lebanon and other countries where main Christian denomination is Orthodox, Christmas Day is observed on 25th of December by Julian Calendar, which happens to be 7th of January. Additionally, in countries like Russia they observe an extra "New Year Day", which comes a week after Christmas (New Year eve is on 13 of January). Since in Russia there are Christians of other denominations besides Orthodox, they get to observe Christmas Day twice and New Year Day twice (all 4 days are official holidays, the offices are closed) 😀
Oleg
November 9, 2010 at 12:50 am
timothy bates (11/8/2010)
Should I get an error or count 2 instead of 3? :hehe:
Allthough not explicitly documented, the SQL Server data type supports the Gregorian calendar. The SET LANGUAGE affects the default date format, but does not change the calendar. Though the Greeks do not have September 21, 1916 in their national calendar, Gregorian September 21, 1916 does exist even in Greece (and equates to September 8, 1916 in the Greece national calendar).
If SQL Server would observe the national Greece calendar, it should also support the Bahá'í, Buddhist, Chinese, Eastern Christian, Ethiopian, Hebrew, Hindu, Indian, Iranian, Islamic, and Thai Solar calendars (*hopes he didn't miss a calendar*)
And based on the article you linked to, SET LANGUAGE Dutch should result in a follow-up question to determine the exact province... :w00t:
Oleg Netchaev (11/8/2010)
should be count(*) or even better count(1)
Why would that be better?
November 9, 2010 at 1:24 am
Hugo Kornelis (11/9/2010)
If SQL Server would observe the national Greece calendar, it should also support the Bahá'í, Buddhist, Chinese, Eastern Christian, Ethiopian, Hebrew, Hindu, Indian, Iranian, Islamic, and Thai Solar calendars (*hopes he didn't miss a calendar*)
Don't forget Mayan calendar! Some two years to go, and ...
😀
And the discussion about eastern calendars remembered me a joke:
Lived Lenin? Yes, but his true name was Ulyanov.
Lived Stalin? Yes, but his true name was Djugashvili.
Was a Great October Socialist Revolution? Yes, but it happened in November.
And it is all the same.
November 9, 2010 at 1:29 am
Don't forget Mayan calendar! Some two years to go, and ...
😀
I agree! :-D:-D:-D
November 9, 2010 at 3:27 am
Question is average but conversation about the topic & date format is great. Thanks
Thanks
November 9, 2010 at 3:50 am
DhruvShah (11/7/2010)
yes Different region will have different answers but when no regions mentioned we normally take US as a default region specially when we talk about dates.
No WE certainly don't - especially when talking about dates.
I assume that when the writer uses the word 'we' without clarifying his default group then his default meaning is 'I'.
--------------------------------------------------------------
“Doubt is not a pleasant condition, but certainty is absurd.” Voltaire
November 9, 2010 at 8:54 am
Hugo Kornelis (11/9/2010)
Oleg Netchaev (11/8/2010)
should be count(*) or even better count(1)Why would that be better?
Simply due to sanity reasons. I heard the rumor way back when in the dark years of my career when I had to work with Oracle that it was possible that count(*) extended to columns' sniffing, which was a pure waste in most cases, and therefore, it was best to avoid it. I believe that in those days it was possible to make the select count(*) from the_table statement break miserably should the calling user be denied of select on one of the columns in the table. In a mean time, count(1) guaranteed to return accurate count regardless of the columns and their respective nullability. Actually, Oracle developers of the last millennium frequently preferred to use count('X'), which offered superior performance when compared to
select count(anything_star_included_but_not_the_nullable_column_name) from the_table;
While this is true that in newer versions of Oracle count('X') is no longer special, and in SQL Server it does not make any difference whether count(*) or count(1) is used, many people like me still opt to avoid count(*) at all costs, just for sanity check if anything.
I understand that in SQL Server if the user executing the statement has explicit deny on any of the columns then the statement will fail regardless of whether count(1) or count(*) was used, but this is a rare scenario, which will force such user to use something like
select count(not_nullable_column) from the_table;
In this regard, there is no difference between count(1) and count(*), but I still like count(1) 🙂
For example, if I have a user named TestUser in AdventureWorks database and the user has explicit select permission on HumanResources.Department table and explicit deny on ModifiedDate column like this:
grant select on HumanResources.Department to TestUser;
go
deny select on HumanResources.Department (ModifiedDate) to TestUser;
go
-- then
exec ('select count(DepartmentID) RecordCount
from HumanResources.Department') as user = 'TestUser';
-- runs just fine, returning 16 as RecordCount, but either
exec ('select count(1) RecordCount
from HumanResources.Department') as user = 'TestUser';
-- or
exec ('select count(*) RecordCount
from HumanResources.Department') as user = 'TestUser';
fails with:
Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column 'ModifiedDate' of the object 'Department', database 'AdventureWorks', schema 'HumanResources'.
Oleg
November 9, 2010 at 2:33 pm
Peter Rijs (11/5/2010)
I agree with Bob: how should we know which date 40358 is, without executing a command?Poor question, poor explanation as well (as pointed out in some other reactions).
Yes, I agree. Not just sour grapes. For those who happened to be correct that 40358 corresponds to a valid date, and a date that is one of the ones mentioned, it is hard to see how to get this answer aside from guessing or having chanced upon this value while working on something else.
Having said that, I picked 3 as the answer because I didn't look carefully that one date was 01/07/2010 and the other was 07/01/2010 -- which should have knocked out 3 as an answer right off the bat -- so I had different problems anyway. But I do think for those who are more observant, I think the 40358 critique still applies. 🙂
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 9, 2010 at 5:27 pm
Oleg Netchaev (11/9/2010)
Hugo Kornelis (11/9/2010)
Oleg Netchaev (11/8/2010)
should be count(*) or even better count(1)Why would that be better?
Simply due to sanity reasons.
(...)
While this is true that in newer versions of Oracle count('X') is no longer special, and in SQL Server it does not make any difference whether count(*) or count(1) is used, many people like me still opt to avoid count(*) at all costs, just for sanity check if anything.
Hi Oleg,
I assumed that was the case. The reason I always prefer to use SELECT * in a COUNT (unless I relaly need to know the number of non-NULL values in a column) is that this clearly documents, a lot better than any other option, that I am counting ROWS, not VALUES.
And there is indeed no performance difference. THis might have been true in the dark ages, and on Oracle, but definitely not on current SQL Server versions.
November 12, 2010 at 7:06 am
with german settings,I got
datefield intField
2010-07-01 00:00:00.0001
2010-01-07 00:00:00.0002
2010-01-07 00:00:00.0003
and a result of 1, not 2.
Viewing 10 posts - 46 through 54 (of 54 total)
You must be logged in to reply to this topic. Login to reply