February 7, 2012 at 10:14 am
Hi there,
I have a function listed below:
[invoice date] between (getdate() - 29) and (getdate() - 1)
Right now, this function will take into account the current time to get the range for invoice date. I'd like to get rid of the time and have the dates perimeter be inclusive of the entire days that are stated as the range. What is the best way to do this?
February 7, 2012 at 10:20 am
The magic word is DATEPART my friend. Use it 😀
February 7, 2012 at 10:45 am
You can only return one part (day, month, or year) of the date with datepart, not the whole date
February 7, 2012 at 10:45 am
To get the the beginning of the "from" date, CAST the first expression to a DATE datatype; that will strip the time portion completely.
To get to the end of the "to" date, you have several options. You can subtract 1 second (or whatever precision you need) from the next day. In your example, the "to" date was "GETDATE() - 1" which is yesterday. Subtract one from your number (1 - 1 = 0: today), then subtract one second, which gets you to yesterday at 11:59:50 pm.
I solved the problem by creating a function that does that job, so I just call the function.
SETANSI_NULLS ON
SETANSI_PADDING ON
IFOBJECTPROPERTY(OBJECT_ID('EndOfDay'), 'IsInlineFunction') IS NOT NULL
DROP
FUNCTIONEndOfDay
GO
CREATE
FUNCTIONEndOfDay
(
@DateTimeDATETIME
)
RETURNSDATETIME
--WITHENCRYPTION
AS
----------------------------------------------------------------------------------------------------
/*<summary>
Returns the last possible moment in the given day
</summary>*/
-- History:Author:Revision:
--2004.04.23Jonathan FaheyCreation
----------------------------------------------------------------------------------------------------
-- SELECT dbo.EndOfDay('2003-05-02 23:45') 'EndOfDay'
BEGIN
RETURNCONVERT(DATETIME, CONVERT(VARCHAR(10), @DateTime, 121) + ' 23:59:59.997')
END
GO
GRANT EXECUTE ON EndOfDay TO <group name here>
GO
Thus, the expression becomes:
[invoice date] between (getdate() - 29) and dbo.EndOfDay(getdate() - 1)
I also have a StartOfDay function, which should be simple to create from the example above. I'll leave that as an exercise for the reader.
February 7, 2012 at 10:48 am
I agree that it will not give you the entire date. But some manipulation and concatenation can get you there. Though it might not be optimal, it will get the job done.
February 7, 2012 at 11:29 am
This?
DECLARE @Date1 DATE = GETDATE()-1
,@Date2 DATE = GETDATE()-29
SELECT @Date1 , @Date2
[invoice date] BETWEEN @Date1 AND @Date2
February 7, 2012 at 5:18 pm
ColdCoffee (2/7/2012)
This?
DECLARE @Date1 DATE = GETDATE()-1,@Date2 DATE = GETDATE()-29
SELECT @Date1 , @Date2
[invoice date] BETWEEN @Date1 AND @Date2
This code will not work because it stops at the beginning of the "to" date but does not include it. For example, if we are looking for activity in January, we need to start at
'2012-01-01 00:00:00.000'
and stop at
'2012-01-31 23:59:59.997'
The code quoted above stops at
'2012-01-31 00:00:00.000'
which means that anything that happens after the first second on the day of the 31st will be excluded.
February 7, 2012 at 5:24 pm
Don't use between.
Use something along these lines:
DECLARE @param1 DATETIME, @param2 DATETIME
SELECT @param1 = '20120101',
@param2 = '20120129'
SELECT * FROM tbl WHERE SomeDateField >= @param1 AND SomeDateField < DATEADD(dd, 1, @param2)
Between is inclusive, to get exclusive you'll have to move forward a day and use a dual component where clause.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 7, 2012 at 5:26 pm
fahey.jonathan (2/7/2012)
ColdCoffee (2/7/2012)
This?
DECLARE @Date1 DATE = GETDATE()-1,@Date2 DATE = GETDATE()-29
SELECT @Date1 , @Date2
[invoice date] BETWEEN @Date1 AND @Date2
This code will not work because it stops at the beginning of the "to" date but does not include it. For example, if we are looking for activity in January, we need to start at
'2012-01-01 00:00:00.000'and stop at
'2012-01-31 23:59:59.997'The code quoted above stops at
'2012-01-31 00:00:00.000'which means that anything that happens after the first second on the day of the 31st will be excluded.
I did not read the complete post of the OP.. My bad :pinch: i just readh the title and the WHERE clause and suggest a datatype change..
Note to self: Read the Full Post, carefully, before even thinking about answering :ermm:
{Edit : Wrong quote}
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply