March 10, 2008 at 9:35 pm
Agreed... guess I need to wipe some of the egg off my face. DOB columns would be an appropriate place to use DATE only (although I don't know if there's a performance hit for implicit conversions with GETDATE() for things like "aging" code). I can think of a couple of other places, now that you mention it. A bit of an overreaction to new toys on my part because it seems like folks want to use them even when they aren't appropriate... like the insanity that seemed to occur when people finally figured out how to write CLR's or how to actually use Hibernate...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2008 at 11:19 pm
The reason I don't / can't use all the new bits (CTEs would be nice for readability in some of my code) is that not all customers can just switch to the new SQL Server 🙁
Using >= and < for dates is always better in my opinion. If you have tables with validity periods as a start/finish pair of columns you can more easily find cases where items start as another finishes using a simple = operation, etc. There's never any ambiguity. I agree that between "reads" better but having to do things like dateadd 3 milliseconds ruins any "niceness" in your code anyway 🙂
March 11, 2008 at 8:38 am
Wow, thanks for the great information. The Date columns are declared as DATETIME. I format them in the code to only display the date (01/01/2008).
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 12, 2008 at 7:31 pm
call me ignorant :w00t: - but given that all years start on 1 Jan and end on 31 Dec couldn't these just be explicitly concatenated to the year that was passed into the proc?
i.e. for the start date: set @startdate = '01/01/'+@year
and for the end date: set @enddate = '12/31'+@year
here's my test code:
declare @startdate as datetime
declare @year as varchar(4)
set @year = '2007'
set @startdate = '01/01/'+@year
select @startdate
...or am i missing something here?
March 12, 2008 at 9:43 pm
There are many ways to get this done - but, depending on where this is being done can have a performance impact.
One of the faster methods of getting the first of the year:
SELECT DATEADD(year, DATEDIFF(year, 0, getdate()), 0); -- first of this year
SELECT DATEADD(year, DATEDIFF(year, 0, getdate()) + 1, 0); -- first of next year
SELECT DATEADD(year, DATEDIFF(year, 0, getdate()) - 1, 0); -- first of last year
You can then use then in the where clause to get last years data:
WHERE date_column >= DATEADD(year, DATEDIFF(year, 0, getdate()) - 1, 0)
AND date_column < DATEADD(year, DATEDIFF(year, 0, getdate()), 0)
If all you need to do is strip the time, then you can use the following:
DATEADD(day, DATEDIFF(day, 0, getdate()), 0)
The above works with any option you can pass to DATEADD and DATEDIFF. If you want to strip the seconds - use minutes, if you want to strip minutes - use hours, etc... Note: if you want to strip seconds - you need to change the 0 date to a date closer to today (e.g. '20080101').
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 12, 2008 at 10:04 pm
Getting back to the OP's question (assuming they had a reason for wanting the first and last day of the year), and if the year is passed as an integer instead of a character string:
declare @year int
set @year=2007
select
YearStart = dateadd(yy,@year-1900,0),
YearEnd = dateadd(yy,@year-1899,-1)
Results:
YearStart YearEnd
----------------------- -----------------------
2007-01-01 00:00:00.000 2007-12-31 00:00:00.000
March 12, 2008 at 10:26 pm
jeff.williams3188 (3/12/2008)
.... Note: if you want to strip seconds - you need to change the 0 date to a date closer to today (e.g. '20080101')...
These methods work over the entire range of datetime values:
select
StartOfHour= dateadd(hh,datediff(hh,0,dt),0),
StartOfMinute= dateadd(ms,-(datepart(ss,dt)*1000)-datepart(ms,dt),dt),
StartOfSecond= dateadd(ms,-datepart(ms,dt),dt)
from
(-- Test Data
select DT = convert(datetime,'99991231 23:59:59.997')
) a
Results:
StartOfHour StartOfMinute StartOfSecond
----------------------- ----------------------- -----------------------
9999-12-31 23:00:00.000 9999-12-31 23:59:00.000 9999-12-31 23:59:59.000
This fails on an overflow:
Select
StartOfMinute= dateadd(minute,datediff(minute,0,dt),0)
from
(-- Test Data to demo overflow
select DT = convert(datetime,'99991231 23:59:59.997')
) a
Results:
Server: Msg 535, Level 16, State 1, Line 1
Difference of two datetime columns caused overflow at runtime.
The logic in these functions work for the widest possible range of values of datetime; most work with any datetime value:
Start of Time Period Functions:
March 13, 2008 at 8:24 am
Well it came down that I need to have up to the minute:
02/29/2008 11:59:59 PM
So when I run this:
SET @MonthStartDt = DATEADD(month, DATEDIFF(month, 0, @StartDate), 0)
Set @MonthEndDt = DATEADD(month, DATEDIFF(month, -1, @EndDate), -1)
I get:
2008-02-01 00:00:00.000
2008-02-29 00:00:00.000
But I need
2008-02-29 11:59:59
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 13, 2008 at 9:46 am
I may be a little late but these are some of the date/time functions, I have found over the years. I hope it helps.
DECLARE @Date AS DateTime
SET @Date = GETDATE()
SELECT
DateAdd(day, DateDiff(day, 0, @Date), 0) AS DayStart,
DateAdd(second, -1, DateAdd(day, DateDiff(day, 0, @Date)+1, 0) ) AS DayEnd,
DateAdd(week, DateDiff(week, 0, @Date), 0) AS WeekStart,
DateAdd(second, -1, DateAdd(week, DateDiff(week, 0, @Date)+1, 0) ) AS WeedEnd,
DateAdd(month, DateDiff(month, 0, @Date), 0) AS MonthStart,
DateAdd(second, -1, DateAdd(month, DateDiff(month, 0, @Date)+1, 0) ) AS MonthEnd,
DateAdd(year, DateDiff(year, 0, @Date), 0) AS YearStart,
DateAdd(second, -1, DateAdd(year, DateDiff(year, 0, @Date)+1, 0) ) AS YearEnd
March 13, 2008 at 9:55 am
alorenzini (3/13/2008)
Well it came down that I need to have up to the minute:02/29/2008 11:59:59 PM
So when I run this:
SET @MonthStartDt = DATEADD(month, DATEDIFF(month, 0, @StartDate), 0)
Set @MonthEndDt = DATEADD(month, DATEDIFF(month, -1, @EndDate), -1)
I get:
2008-02-01 00:00:00.000
2008-02-29 00:00:00.000
But I need
2008-02-29 11:59:59
No! You really don't and the 23:59:59 is a form of "Death by SQL". What you need is...
SET @MonthStartDt = DATEADD(month, DATEDIFF(month, 0, @StartDate), 0)
Set @MonthEndDt = DATEADD(month, DATEDIFF(month, 0, @EndDate)+1, 0)
... and your WHERE clauses should look like this...
WHERE somedatecol >= @MonthStartDt
AND somedatecol < @MonthEndDt
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2008 at 10:01 am
And I will second Jeff's assertion.
😎
March 13, 2008 at 1:02 pm
I think I found the problem with.
The actual table is storeing the endDate as 2008-03-31 22:59:59.000
but I have declared my @EndDt variable as DateTime which is stored as 2008-03-31 00:00:00.000
How can I get around this?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 13, 2008 at 1:04 pm
Art - use Jeff's suggestion a few posts up. It's precisely to deal with issues just like that.
Meaning - use a "less than april first" syntax.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 13, 2008 at 1:50 pm
Works like a champ. Thanks for eveybody's insight. Shouldn't have another question about datetime function for a while now. 😀
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply