returning data for a given month

Question

returning data for a given month

Viewing 3 posts - 16 through 17 (of 17 total)

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

That would be the one... they're pretty smart making it a new datatype... will actually keep 23:59:59.997 code from breaking... unless it's used on the wrong column type.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 2

Jeff: Good point. I hadn't thought of that.

I just checked two execution plans, using both methods. They were identical, so no performance reason to use either. So that means the >= and < is better. I'll keep that in mind.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

Just to flog this topic a little more.

When you are using text stings for dates, you should use the format 'YYYYMMDD HH:MM:SS.MIL'

Example: '20080116 18:01:25.997' (or '20080116' for date only)

This is because it is always interpreted the same by SQL Server and is not affected by the setting of DATEFORMAT or LANGUAGE.

print 'set dateformat mdy'

set dateformat mdy -- Standard US English

select [YYYYMMDD] = convert(datetime,'20080116')

select [YYYY-MM-DD] = convert(datetime,'2008-01-16')

print 'set dateformat ydm'

set dateformat ydm -- Something a little different

select [YYYYMMDD] = convert(datetime,'20080116')

-- Give error

select [YYYY-MM-DD] = convert(datetime,'2008-01-16')

Results:

set dateformat mdy

YYYYMMDD

------------------------------------------------------

2008-01-16 00:00:00.000

(1 row(s) affected)

YYYY-MM-DD

------------------------------------------------------

2008-01-16 00:00:00.000

(1 row(s) affected)

set dateformat ydm

YYYYMMDD

------------------------------------------------------

2008-01-16 00:00:00.000

(1 row(s) affected)

Server: Msg 242, Level 16, State 3, Line 12

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.