Current Date minus one???
-
Hi nice list people,
Please help me ... deadline tomorrow morning.
I need to run a procedure nightly, just after midnight to collect the previous days peak.
So I need to get yesterdays date and concatonate 0:00 and then also 24:00 as the time to this date to query the range.
Here is a SP I have been using but I need to subsitute @StarDate and @EndDate of course:
CREATE PROCEDURE dbo.TrendMax
@TID int,
@StartDate datetime,
@EndDate datetime
AS
SELECT top 1 DATE_STAMP_ "Date_Time", max(CONVERT(decimal(19,4), DATA_VALUE_ )) "Value"
FROM TRENDDATA
WHERE TID_ = @TID AND @StartDate <= DATE_STAMP_ AND @EndDate >= DATE_STAMP_ AND RECORD_TYPE_ = 2
group by DATE_STAMP_
order by max(CONVERT(decimal(19,4), DATA_VALUE_ )) desc
GO
Any Ideas???
-
-- There are a lot of possible solutions. The following is only one of them.
DECLARE @dtmEnd AS DATETIME,
@dtmStart AS DATETIME
SET @dtmEnd = FLOOR(CAST(GETDATE() AS FLOAT))
SET @dtmStart = DATEADD(d,-1,@dtmEnd)
PRINT @dtmEnd
PRINT @dtmStart
/*
The logic behind this is, that SQL-Server stores any date as a floating point value,
where the decimal part of the number represents the time portion.
As time is always "00:00:00" at the begin of a day, FLOOR does the job.
You need of course not define those variables. This is for clarity only.
You need no start date parameter, if your always checking one whole day.
*/
CREATE PROCEDURE dbo.TrendMax
@TID int,
@StartDate datetime,
@EndDate datetime
AS
DECLARE @dtmEnd AS DATETIME,
@dtmStart AS DATETIME
SET @dtmEnd = FLOOR(CAST(@EndDate AS FLOAT))
SET @dtmStart = DATEADD(d,-1,@dtmEnd)
SELECT top 1
DATE_STAMP_ "Date_Time",
max(CONVERT(decimal(19,4), DATA_VALUE_ )) "Value"
FROM TRENDDATA
WHERE
TID_ = @TID AND
AND RECORD_TYPE_ = 2
AND @dtmStart <= DATE_STAMP_
AND @dtmEnd > DATE_STAMP_
group by DATE_STAMP_
order by max(CONVERT(decimal(19,4), DATA_VALUE_ )) desc
regards, _/_/_/ paramind _/_/_/
PS: Logically, you should not resort to the "TOP"-statement, whenever possible, because you might - in this case - miss the information, that there are two or more equal peaks on the day in question.
/* Futhermore, there's no need for an expensive grouping and ordering */
SELECT
DATE_STAMP_ "Date_Time",
CONVERT(decimal(19,4), DATA_VALUE_)) "Value"
FROM TRENDDATA AS T1
WHERE
DATA_VALUE_ = (SELECT MAX(DATA_VALUE_)
FROM TRENDDATA AS T2
WHERE
TID_ = @TID AND
AND RECORD_TYPE_ = 2
AND @dtmStart <= DATE_STAMP_
AND @dtmEnd >= DATE_STAMP_)
AND TID_ = @TID
AND RECORD_TYPE_ = 2
AND @dtmStart <= DATE_STAMP_
AND @dtmEnd >= DATE_STAMP_
_/_/_/ paramind _/_/_/ -
I don't mean to high-jack the post or anything, but how come when converting a DATETIME variable to a FLOAT there is no error thrown? There are '-' characters in the DATETIME variable, so how can it get converted to a FLOAT?
Tryst
-
Do you think there are "."-characters stored in floating point values? ;P
Of course not. The internal storage is always completely different from what you see. Never wondered how SQL copes with international date formats?
regards, _/_/_/ paramind _/_/_/
_/_/_/ paramind _/_/_/ -
There are no hyphens in DATETIME datatype. Check the BOL. Quote: Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. :End Quote
Taking 2006-02-27 12:00:00.000 and making it DATETIME, changes it to those two 4-byte integers which then can be 'Float'ed.
-SQLBill
-
The only possible error in this script - people like to record datetime without time portion.
That's why you gonna take 2 ddays instead on one.
Small chane will fix it:
DECLARE @dtmEnd AS DATETIME,
@dtmStart AS DATETIME
SET @dtmEnd = FLOOR(CAST(GETDATE() AS FLOAT))
SET @dtmStart = DATEADD(d,-1,@dtmEnd)
SET @dtmEnd= DATEADD(ms,-3,@dtmEnd)
Then you can use simple BETWEEN @dtmStart and @dtmEnd
_____________
Code for TallyGenerator -
If you just want your script to give you info from yesterday no matter what today is, you don't need FLOAT and you don't need to pass a date. I don't have a clue what the variable @TID is for but here's your original script modified to always work for yesterday...
CREATE PROCEDURE dbo.TrendMax
@TID INT
AS
SELECT TOP 1
Date_Stamp AS Date_Time,
MAX(CONVERT(DECIMAL(19.4),Data_Value_)) AS Value
FROM TrendData
WHERE TID_ = @TID
AND Record_Type_ = 2
AND Date_Stamp_ >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0) -- >= Start of yesterday at midnight
AND Date_Stamp_ < DATEADD(dd,DATEDIFF(dd,0,GETDATE() ),0) -- < Start of today at midnight
ORDER BY MAX(CONVERT(DECIMAL(19.4),Data_Value_)) DESC
GO
--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) -
Hello. I don't agree. First, people have no choice
SQL-Server doesn't care, whether one ENTERS a time portion. That doesn't change the storage of the time portion at all, once you've defined a field or a variable as DATETIME. _/_/_/
_/_/_/ paramind _/_/_/ -
Don't agree with what?
If to assign:
SET @StartDate = '2006-02-27'
SET @EndDate = '2006-02-28'
then
BETWEEN @StartDate AND @EndDate
will actually take records for 2 days, not one, if no time portions recorded (I mean time portion = 00:00:00.000)
But id @EndDate = 3ms before '2006-02-28' then BETWEEN will take all records for Feb 27 and only records for that day.
_____________
Code for TallyGenerator -
No, I still don't! For an obvious reason - there's no need to
Have a look at the whole thing: I did NOT use the between operator, but the "<"-operator. For good reason
_/_/_/ paramind _/_/_/ -
Just a note about SQL time slices. SQL Server uses 3 millisecond increments and rounds up. The last possible time in a day is 23:59:59.997.
-
Are you sure?
What about this?
SELECT
DATE_STAMP_ "Date_Time",
CONVERT(decimal(19,4), DATA_VALUE_)) "Value"
FROM TRENDDATA AS T1
WHERE
DATA_VALUE_ = (SELECT MAX(DATA_VALUE_)
FROM TRENDDATA AS T2
WHERE
TID_ = @TID AND
AND RECORD_TYPE_ = 2
AND @dtmStart = DATE_STAMP_)
AND TID_ = @TID
AND RECORD_TYPE_ = 2
AND @dtmStart = DATE_STAMP_
That's your script, not mine.
_____________
Code for TallyGenerator -
That's the >furthermore< script, monsieur, illustrating what - CETERIS PARIBUS - is meant by the omission of an unnecessary grouping and ordering, not the proposed statement.
_/_/_/ paramind _/_/_/ -
HI,
I think it will realyhelp you to find a problem of your answer!!
SELECT CONVERT(DATETIME,CONVERT(VARCHAR,'2006-03-01',121) + ' 11:25:25.526 ',121)
I have add the time part to a date field which i have taken first, as a varchar and then add the time part into it and thereafter convert it into a datetime data type.
Regards
AMIT GUPTA
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply