Getdate with previous date

remember getdate returns the time portion of NOW: 2010-03-26 12:18:05.390

you want to start at midnite yesterday, not 12:18 of yesterday.

and another way:

--2010-03-25 00:00:00.0002010-03-25 23:59:59.997

select DATEADD(dd, DATEDIFF(dd,0,getdate()), -1),

DATEADD(ms,-3,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0))

WHERE shipdate

BETWEEN DATEADD(dd, DATEDIFF(dd,0,getdate()), -1)

AND DATEADD(ms,-3,DATEADD(dd, DATEDIFF(dd,0,getdate()), 0))

Lowell, it would be better to use an open interval range check instead of between. It could also cause problems if you are using SQL Server 2008 and the new datetime data type which has a higher precision than 3 milliseconds.

If you look at mister.magoo's post you'll see how he did that.

Also, be very careful changing the date part of the datediff calculation. It is better (IMHO) to subtract the number of days from the results of the datediff instead of using a fixed date. In other words:

DATEADD(day, DATEDIFF(day, 0, getdate()) - 1, 0) instead of DATEADD(day, DATEDIFF(day, 0, getdate()), -1)

i always just use left(getdate(),11). so i would just use

select * from datestable

where mydate between left(getdate() -1,11) and left(getdate(),11)

This is one of the slowest methods available. First, you are relying on implicit conversion of the datetime to a specific representation. Second, this returns a string, that is then implicitly converted back to a datetime. Third, between is inclusive so you would include rows for the current day where the time is midnight. Probably not what is intended.

also, isn't the implicit conversion of left(getdate() -1,11) dependent on the servers regional settings? I think that makes an assumption on the regional settings that might not be true for everyone.

I would agree with that, as a general approach. Subtracting 3 millisconds is technically fine with DATETIME, of course.

Why?

In the spirit of being compatible with the new data types in SQL Server 2008, you should avoid using the subtraction operator directly with dates and times. It only works with DATETIME. Use DATEADD instead. In fact, always use specific date/time manipulation functions where possible.

WHERE ship_date >= DATEADD(DAY, -1, DATEADD(DAY, DATEDIFF(DAY, CONVERT(DATETIME, '19690711', 112), CURRENT_TIMESTAMP), CONVERT(DATETIME, '19690711', 112))),

AND shipdate < DATEADD(DAY, DATEDIFF(DAY, CONVERT(DATETIME, '19690711', 112), CURRENT_TIMESTAMP), CONVERT(DATETIME, '19690711', 112));

Paul

edit: tags

Paul,

If you look at the calculation - it is not performing a calculation on getdate(), but rather on the results from the datediff calculation.

I know you disagree with using 0 as the seed date in this calculation - and understand the reasoning for that. I don't like using a different seed date, mostly because it only works for days and I like having something that works for all.

For example:

DECLARE @seedDate datetime;

SET @seedDate = '20100101';

SELECT DATEADD(day, DATEDIFF(day, @seedDate, GETDATE()) - 1, @seedDate);

SELECT DATEADD(day, DATEDIFF(day, @seedDate, GETDATE()) - 0, @seedDate);

SELECT DATEADD(day, DATEDIFF(day, @seedDate, GETDATE()) + 1, @seedDate);

SELECT DATEADD(month, DATEDIFF(month, @seedDate, GETDATE()) - 1, @seedDate);

SELECT DATEADD(month, DATEDIFF(month, @seedDate, GETDATE()) - 0, @seedDate);

SELECT DATEADD(month, DATEDIFF(month, @seedDate, GETDATE()) + 1, @seedDate);

SELECT DATEADD(year, DATEDIFF(year, @seedDate, GETDATE()) - 1, @seedDate);

SELECT DATEADD(year, DATEDIFF(year, @seedDate, GETDATE()) - 0, @seedDate);

SELECT DATEADD(year, DATEDIFF(year, @seedDate, GETDATE()) + 1, @seedDate);

Instead of adding another calculation around this to add or subtract days, all we are doing adjusting the number of days difference to get the calculation.

So it is. Sneaky bracket! I had to look more than once to see it still. Oh well. Sorry about that.

That code seems to produce the same results regardless of the seed date value, which I would expect. It is 5:40am here and I am very tired...am I missing your point here (again)?

No, you are not missing anything - that is the intent. What is important is the calculation does not depend on changing the seed date. It depends on how you add/subtract to the results of the datediff.

Where the seed date is important is determining which day of the month/year you get. If you want the end of the month all the time, you set the seed date as an end of the year seed (e.g. '20091231').

If you want the 15th of the month, set the seed to the 15th. It works this way for anything above the day increment. For days and less, the seed date doesn't matter at all - just set one and the calculations work.

Yeah - it is. Took me a while to realize that also...

Thanks, Jeffrey.