April 1, 2016 at 5:57 am
Hi,
I need help with the below sql, basically for whatever date that is passed into the variable @dt I want the variables @dtFrom and @dtTo to be calculated as per below:
@dtFrom
minus 1 hour of the variable @dt and the minute and seconds to be 0
if the minutes of the datetime of the @dt variable is above 30 else the minute to be 30 and seconds to 0
@dtTo
minus 1 hour of the variable @dt and the minute to be 59 and seconds to be 59
if the minutes of the datetime of the @dt variable is above 30 else the minute to be 29 and seconds to 59
--DateTime is now '2016-03-31 14:38:00.973'
--Date From = '2016-03-31 13:30:00'
--Date To = '2016-03-31 13:59:59'
--DateTime is now '2016-04-01 12:02:00.973'
--Date From = '2016-04-01 11:00:00'
--Date To = '2016-04-01 11:29:59'
declare @dt datetime
declare @dtFrom datetime
declare @dtTo datetime
Set @dt = '2016-03-31 14:38:00.973'
--GETDATE()
Set @dtFrom = ''
Set @dtTo = ''
Select @dtFrom as [DateFrom],
@dtFrom as [DateTo]
Thanks
April 1, 2016 at 6:46 am
bit convoluted.....may be easier ways
SELECT thedate,
CAST(LEFT( DATEADD(hour,-1, DATEADD(minute, -DATEPART(minute, thedate) % 30, thedate)), 19) AS DATETIME) AS dtFrom,
DATEADD(second, -1, DATEADD(minute, 30, CAST(LEFT( DATEADD(hour,-1, DATEADD(minute, -DATEPART(minute, thedate) % 30, thedate)), 19) AS DATETIME))) AS dtTo
FROM
( -- Test Data
SELECT thedate = '2016-03-31 14:38:00.973' UNION ALL
SELECT thedate = '2016-04-01 12:02:00.973' UNION ALL
SELECT thedate = '2016-04-01 14:00:00.000' UNION ALL
SELECT thedate = '2016-04-01 14:30:00.000'
) x;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 1, 2016 at 6:51 am
This seems to do what you want:
DECLARE @dt datetime;
SET @dt='20160331 14:38:25';
SELECT date_from=DATEADD(mi,(((DATEDIFF(mi,0,@dt)/30)-2)*30),0),
date_to= DATEADD(ss,-1,DATEADD(mi,(((DATEDIFF(mi,0,@dt)/30)-1)*30),0));
SET @dt='20160401 12:02:25';
SELECT date_from=DATEADD(mi,(((DATEDIFF(mi,0,@dt)/30)-2)*30),0),
date_to= DATEADD(ss,-1,DATEADD(mi,(((DATEDIFF(mi,0,@dt)/30)-1)*30),0));
Having said that, what are you planning on using this for?
If you're going to use these resulting values in a search, keep in mind that the range defined by the calculated datetime values will miss anything that happens in that last second of the range, since 11:29:59 is really 11:29:59.000 (for datetime). You would then miss anything that happened between 11:29:59.000 and 11:29:59.997 (again, these specific values are for datetime; for datetime2 they would depend on the precision used).
Cheers!
April 1, 2016 at 7:03 am
Jacob Wilkins (4/1/2016)
This seems to do what you want:
DECLARE @dt datetime;SET @dt='20160331 14:38:25';
SELECT date_from=DATEADD(mi,(((DATEDIFF(mi,0,@dt)/30)-2)*30),0),
date_to= DATEADD(ss,-1,DATEADD(mi,(((DATEDIFF(mi,0,@dt)/30)-1)*30),0));
SET @dt='20160401 12:02:25';
SELECT date_from=DATEADD(mi,(((DATEDIFF(mi,0,@dt)/30)-2)*30),0),
date_to= DATEADD(ss,-1,DATEADD(mi,(((DATEDIFF(mi,0,@dt)/30)-1)*30),0));
Having said that, what are you planning on using this for?
If you're going to use these resulting values in a search, keep in mind that the range defined by the calculated datetime values will miss anything that happens in that last second of the range, since 11:29:59 is really 11:29:59.000 (for datetime). You would then miss anything that happened between 11:29:59.000 and 11:29:59.997 (again, these specific values are for datetime; for datetime2 they would depend on the precision used).
Cheers!
nice Jacob.....not sure where I was with my code 😀
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 1, 2016 at 7:43 am
SQL_Kills (4/1/2016)
Hi,I need help with the below sql, basically for whatever date that is passed into the variable @dt I want the variables @dtFrom and @dtTo to be calculated as per below:
@dtFrom
minus 1 hour of the variable @dt and the minute and seconds to be 0
if the minutes of the datetime of the @dt variable is above 30 else the minute to be 30 and seconds to 0
@dtTo
minus 1 hour of the variable @dt and the minute to be 59 and seconds to be 59
if the minutes of the datetime of the @dt variable is above 30 else the minute to be 29 and seconds to 59
--DateTime is now '2016-03-31 14:38:00.973'
--Date From = '2016-03-31 13:30:00'
--Date To = '2016-03-31 13:59:59'
--DateTime is now '2016-04-01 12:02:00.973'
--Date From = '2016-04-01 11:00:00'
--Date To = '2016-04-01 11:29:59'
declare @dt datetime
declare @dtFrom datetime
declare @dtTo datetime
Set @dt = '2016-03-31 14:38:00.973'
--GETDATE()
Set @dtFrom = ''
Set @dtTo = ''
Select @dtFrom as [DateFrom],
@dtFrom as [DateTo]
Thanks
Unless the data in the columns you be using those range values on will always be accurate only to the second, you could actually be missing out on a whole lot of data especially because it sounds like you want to use BETWEEN to isolate a date range. Don't do that... not even if there is such a guarantee because requirements do change unexpectedly.
Instead, get used to always using the temporally "closed/open" format. Like this...
WHERE SomeDTColumn >= @dtFrom --Inclusive
AND SomeDTColumn < @dtTo --Up to and not including
It's called "bullet proofing your code".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2016 at 9:30 am
This is more like how I would do it and it allows for the temporal closed/open format Jeff showed above.
D E C L A R E -- spaces added to allowing posting from current location
@dt datetime,
@DateFrom datetime,
@DateTo datetime;
SET @dt='20160331 14:38:25';
select @DateFrom = dateadd(hour,-1,dateadd(minute,((datediff(minute,0,@dt)/30))*30,0)),
@DateTo = dateadd(minute,30,@DateFrom);
select @DateFrom, @DateTo;
SET @dt='20160401 12:02:25';
select @DateFrom = dateadd(hour,-1,dateadd(minute,((datediff(minute,0,@dt)/30))*30,0)),
@DateTo = dateadd(minute,30,@DateFrom);
select @DateFrom, @DateTo;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply