Get Previous Date
-
I'm trying to write an extract for a DataWarehouse DB. Essentially, I want to insert into the DW all data from the OLTP DB with the datestamp from the previous day (yesterday).
My Date Column from the OLTP is in the following format:
2004-10-07 15:22:36.000
Can anyone help point me to the best way to accomplish this?
Thanks,
James Press
-
where (date_column > (getdate() -1))
This would get anything entered in the last 24 hours from the datetime your process runs.
Michelle -
You can also look at the dateadd() function in BOL.
-
where convert(varchar(10), date_column, 101) = convert(varchar(10), getdate()-1, 101)
-
If you want to be able to use eventual indices on the datecolumn and if you don't want to supply any parameters, and if you by 'yesterday' mean the day before today (or day before query is executed) between midnight and 23:59.999, then you have to do like this;
where dateColumn >= convert(char(8), dateadd(day, -1, getdate()), 112)
and dateColumn < convert(char(8), getdate(), 112)
/Kenneth
-
Basically the same as Kenneth, but this time using only integer operations
WHERE
>= DATEADD(d,DATEDIFF(d,1,getdate()),0)
AND
<DATEADD(d,DATEDIFF(d,0,getdate()),0)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
WOW!!!
Thanks for all the suggestions!!!
Now I will have to make a decision on which one works best for my needs.
-JP
-
I'd use Frank's suggestion as it is operation based and not conversion based (much faster on large sets).
-
I'll second that... Frank's method also allows for indexes to be used on the date columns where a conversion sometimes does not.
--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) -
If you read closer, you'll notice that the conversion is on the parameter side, not the column itself - so indices will likely get used. It's the other way around that's the 'bad' way. And, any function may have the effect to invalidate index usage - it's not limited to convert only in any way.
For performance - well, I've done some testing... The 'function approach' is indeed slightly faster than convert - though no way as much as some would like to believe. If the difference in performance should become an issue, you have other problems as well.
Which to use then? Well, I'd say that the first requirement is that the one writing it should fully understand what he/she is writing, the second should be that it shouldn't be too hard on the next person coming along having to maintain the code. (ie maintainability)
For that reason, I personally prefer using the convert construct over the dateadd one..
Hand up, everyone who in detail can explain exactly what DATEADD(d,DATEDIFF(d,1,getdate()),0) does, and why it works the way it does. Only then imho should you use it.
...and Frank isn't allowed to answer
/Kenneth
-
...well then
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Ok, Ken, here ya go...
DATEADD(d,DATEDIFF(d,1,getdate()),0)
DATEDIFF(d,1,getdate()) takes the difference between the current date and 01/02/1900 and converts it to a whole number of days. Because "1" was used instead of "0", you've effectively subtracted one day from the number of days it's been since 01/01/1900.
DATEADD(d,somenumber,0) adds "somenumber" to "0" days and converts it back into a date sans any time element that may have been present in the original date. Since "0" represents 01/01/1900, the orginal date of "somenumber" would be restored minus the time element. But, in this case, "somenumber" is the DATEDIFF from the above paragraph and that represents one day LESS the GetDate(). The result produced by the entire formula is yesterday's date sans any time element.
I partially agree with you about learning what a function does before you use it kinda like if you don't know how to multiply, why are you using a calculator? It's been a very long time since I've done the long-hand calculation for precisely calculating the square-root of something (yes, there's a method). I don't imagine that most folks have done that same calculation but they still use the square-root key on the calculator. It's kinda like date functions... sure, those that know precisely how something works will always be more effective and innovative but, just like the square-root key, when a bunch of knowledgable folks post that some given formula does some useful task and people like yourself and Frank all agree, then why wouldn't a newbie or someone with a little less knowledge use the function to do what they need without learning the precise "gazintas"? Ever buy third party software? Unless you've reversed engineered precisely how something like Word makes a table of contents or you know how to make a postal barcode at the code level or you know precisely how the computer and fuel injectors in your car work, I'd say ease up a bit on the use of "black box" functions.
By the way, I do thank you for the testing you've done between CONVERT and the DATEDIFF/DATEADD functions. I've always been in favor of staying away from any character conversions in favor of Integer Math because I know that character conversions are inherently slower. I didn't know there was so little difference and you've prompted me to construct my own quantitative test(s).
For anyone else who needs a couple of "black box" date functions, here's a few of my own. Do understand that Ken is correct in saying you should really study these and learn what's going on so that you don't make a mistake with rounding errors when trying to make your own funtions in the future. Some of the functions discretely use things like +1 and -1 instead of Frank's "01/02/1900' method just to make the offset apparent. They certainly could be modified/simplified for production but, since I use these to teach my team-mates, I wanted the offsets to be real apparent.
Since I do quite a bit of work in the world of telephone billing, you might find some of these functions particullary useful if you are too. You'll notice that I use many different methods to do the necessary convertions and that some of them do, indeed, involve the use of CONVERT because it was more effective for one reason or another (usually readability and compactness of code). Enjoy...
DECLARE @Date DATETIME SET @Date = '2005-01-04 23:18:51.873'
DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME SET @StartDate = '2005-01-04 23:18:51.873' SET @EndDate = @StartDate + '16:20:37'
SET NOCOUNT ON PRINT 'Current Date: ' + CONVERT(VARCHAR(30),@Date,121) PRINT 'Current Start Date: ' + CONVERT(VARCHAR(30),@StartDate,121) PRINT 'Current End Date: ' + CONVERT(VARCHAR(30),@EndDate,121) PRINT ' '
------ Finds the date without the time (Time=00:00:00.000) SELECT DATEADD(dd,0,DATEDIFF(dd,0,@Date)) AS DateOnly
------ Finds the time without the date (01/01/1900) (FORGET WHAT THE OUTPUT LOOKS LIKE) SELECT @Date-DATEADD(dd,0,DATEDIFF(dd,0,@Date)) AS TimeOnlyNoFormat
------ Finds the FIRST day of PREVIOUS month (Time=00:00:00.000) SELECT DATEADD(mm,DATEDIFF(mm,0,@Date)-1,0) AS FirstDayPreviousMonth
------ Finds the FIRST day of CURRENT month (Time=00:00:00.000) SELECT DATEADD(mm,DATEDIFF(mm,0,@Date)+0,0) --(The "+0" left in for comparison of code) AS FirstDayCurrentMonth
------ Finds the FIRST day of NEXT month (Time=00:00:00.000) SELECT DATEADD(mm,DATEDIFF(mm,0,@Date)+1,0) AS FirstDayNextMonth
------ Finds the LAST day of PREVIOUS month (Time=23:59:59.997) (resolution is 3 ms) SELECT DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,@Date)+0,0)) --(The "+0" left in for comparison of code) AS LastDayPreviousMonth
------ Finds the LAST day of CURRENT month (Time=23:59:59.997) (resolution is 3 ms) SELECT DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,@Date)+1,0)) AS LastDayCurrentMonth
------ Finds the LAST day of NEXT month (Time=23:59:59.997) (resolution is 3 ms) SELECT DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,@Date)+2,0)) AS LastDayNextMonth
------ Finds the difference between two dates and displays in HHHHH:MM:SS format SELECT CONVERT(VARCHAR(10),DATEDIFF(hh,0,@EndDate-@StartDate)) --Converts the hours +RIGHT(CONVERT(CHAR(8),@EndDate-@StartDate,108),6) --Converts mins & secs AS [HHHHH:MM:SS]
--==== Numeric difference between two dates ==============================================
------ Rounded decimal hours (HHHHH.H) SELECT CONVERT(DECIMAL(28,1), --Does the formatting (CONVERT(FLOAT,@EndDate-@StartDate)*24.0)) --Calcs decimal hours (rounding controlled) AS RoundedDecimalHours
------ Rounded whole hours (HHHHH) SELECT CONVERT(DECIMAL(28,0), --Does the formatting (CONVERT(FLOAT,@EndDate-@StartDate)*24.0)) --Calcs decimal hours (rounding controlled) AS RoundedWholeHours
------ Unrounded whole hours (HHHHH) SELECT DATEDIFF(hh,0,@EndDate-@StartDate) AS UnroundedWholeHours
SELECT DATEDIFF(hh,@StartDate,@EndDate) --Do not use... rounds up unexpectedly AS UnroundedWholeHoursBAD
------ Rounded decimal minutes (MMMMM.M) SELECT CONVERT(DECIMAL(28,1), --Does the formatting (CONVERT(FLOAT,@EndDate-@StartDate)*24.0*60.0)) --Calcs decimal minutes (no chance of overflow) AS RoundedDecimalMinutes
------ Rounded whole minutes (MMMMM) SELECT CONVERT(DECIMAL(28,0), --Does the formatting (CONVERT(FLOAT,@EndDate-@StartDate)*24.0*60.0)) --Calcs decimal minutes (no chance of overflow) AS RoundedWholeMinutes
------ Unrounded whole minutes (MMMMM) SELECT DATEDIFF(mi,0,@EndDate-@StartDate) --Greatly reduced chance of overflow AS UnroundedWholeMinutes
------ Whole minutes rounded UP (MMMMM) SELECT CONVERT(DECIMAL(28,0), --Does the formatting CONVERT(FLOAT,@EndDate-@StartDate)*24.0*60.0 --Calcs decimal minutes +.49999) --Rounds up to nearest minute AS WholeMinutesRoundedUp
------ Decimal minutes rounded up to 6 second increments SELECT CONVERT(DECIMAL(28,1), --Does the formatting CONVERT(FLOAT,@EndDate-@StartDate)*24.0*60.0 --Calcs decimal minutes (no chance of overflow) +.049999) --Rounds up to nearest tenth AS DecimalMinutesRoundedUp6SecInc
------ Whole seconds SELECT ROUND(DateDIFF(ss,0,@EndDate-@StartDate),0) --Returns duration as seconds (minor chance of overflow AS WholeSeconds --but never in the telephone business)
--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) -
>>>Hand up, everyone who in detail can explain exactly what DATEADD(d,DATEDIFF(d,1,getdate()),0) does, and why it works the way it does. <<<
That, is a VERY WELL KNOWN TRICK in this forum
Just have to be around enough to see it come out every so often even with the explanation of it
* Noel -
Then I might as well explain a litle further why I asked the question the way I did
As I said earlier in the thread, I personally prefer the convert variant before the dateadd/diff nested functions, and there's some very simple reasons for this.
1) Readability - as noted, it's not quite that simple to actually see what something like DATEADD(d,DATEDIFF(d,1,getdate()),0) would return, or why it returns what it does. It works, for sure, it's just not that easy to understand, the convert syntax is imo much easier.
2) Explicitness - ie depending and relying on defaults. The dateadd/diff approach depends on implicit default behaviour, whereas the convert style doesn't. Personally, I try to always stay away from everything that may (or will) change (and most likely break), no matter how sexy it might seem.
3) Ease of change / debugging - much the same reasons as listed under 1)
..of course, it's just my .02 anyway
/Kenneth
-
These are all sane and valid arguments, Kenneth!
So, it depends...(err, stealing your phrase
)Now, as a DATETIME is currently implemented, and to add to the confusion, I suspect this to give a VERY good performance, if you want to get rid of the time portion:
SELECT
CAST(CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) AS INT) AS DATETIME)
Probably even better than DATEADD(DATEDIFF...))
Sorry, couldn't resist to jump here right on, though you told me not to
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply