January 30, 2017 at 12:28 pm
Hello,
Any assistance would be much appreciated!
We just upgraded to SQL Server 2014 but when I tried running a good query that was able to run on SQL Server 2005, I get the following error:
Msg 529, Level 16, State 2, Line 4
Explicit conversion from data type datetime2 to float is not allowed.
I've attached the troublesome portion:
case
when RIGHT('0'+cast(DATEPART(mi,initial_date_time) as varchar(2)),2) between '00' and '14'
then convert(smalldatetime,ROUND(cast(initial_date_time as float) * (24/.5),0)/(24/.5))
when RIGHT('0'+cast(DATEPART(mi,initial_date_time) as varchar(2)),2) between '15' and '29'
then convert(smalldatetime,ROUND((cast(initial_date_time as float)-(15.0/24.0/60.0)) * (24/.5),0)/(24/.5)) --
when RIGHT('0'+cast(DATEPART(mi,initial_date_time) as varchar(2)),2) between '30' and '44'
then convert(smalldatetime,ROUND(cast(initial_date_time as float) * (24/.5),0)/(24/.5))
else convert(smalldatetime,ROUND((cast(initial_date_time as float)-(15.0/24.0/60.0)) * (24/.5),0)/(24/.5)) --
end as initial_date_time
I'm not understanding how to go about correcting this as I'm more of an Oracle SQL guy which is ALOT simpler when it comes to treating date_time type!
January 30, 2017 at 12:58 pm
Tn37355 - Monday, January 30, 2017 12:28 PMHello,Any assistance would be much appreciated!
We just upgraded to SQL Server 2014 but when I tried running a good query that was able to run on SQL Server 2005, I get the following error:
Msg 529, Level 16, State 2, Line 4
Explicit conversion from data type datetime2 to float is not allowed.
I've attached the troublesome portion:case
when RIGHT('0'+cast(DATEPART(mi,initial_date_time) as varchar(2)),2) between '00' and '14'
then convert(smalldatetime,ROUND(cast(initial_date_time as float) * (24/.5),0)/(24/.5))
when RIGHT('0'+cast(DATEPART(mi,initial_date_time) as varchar(2)),2) between '15' and '29'
then convert(smalldatetime,ROUND((cast(initial_date_time as float)-(15.0/24.0/60.0)) * (24/.5),0)/(24/.5)) --
when RIGHT('0'+cast(DATEPART(mi,initial_date_time) as varchar(2)),2) between '30' and '44'
then convert(smalldatetime,ROUND(cast(initial_date_time as float) * (24/.5),0)/(24/.5))
else convert(smalldatetime,ROUND((cast(initial_date_time as float)-(15.0/24.0/60.0)) * (24/.5),0)/(24/.5)) --
end as initial_date_timeI'm not understanding how to go about correcting this as I'm more of an Oracle SQL guy which is ALOT simpler when it comes to treating date_time type!
According to the conversion matrix in this link https://msdn.microsoft.com/en-us/library/ms191530.aspx, that conversion is not allowed.
However, your conversion seems very complex. Maybe there is a simple solution if you explain your goal a little bit more?
Igor Micev,My blog: www.igormicev.com
January 30, 2017 at 1:27 pm
declare @t as table (
initial_date_time smalldatetime
);
insert into @t values('2017-01-30 15:05:00'), ('2017-01-30 15:22:00'), ('2017-01-30 15:37:00'), ('2017-01-30 15:54:00')
select
case
when RIGHT('0'+cast(DATEPART(mi,initial_date_time) as varchar(2)),2) between '00' and '14'
then convert(smalldatetime,ROUND(cast(initial_date_time as float) * (24/.5),0)/(24/.5))
when RIGHT('0'+cast(DATEPART(mi,initial_date_time) as varchar(2)),2) between '15' and '29'
then convert(smalldatetime,ROUND((cast(initial_date_time as float)-(15.0/24.0/60.0)) * (24/.5),0)/(24/.5)) --
when RIGHT('0'+cast(DATEPART(mi,initial_date_time) as varchar(2)),2) between '30' and '44'
then convert(smalldatetime,ROUND(cast(initial_date_time as float) * (24/.5),0)/(24/.5))
else convert(smalldatetime,ROUND((cast(initial_date_time as float)-(15.0/24.0/60.0)) * (24/.5),0)/(24/.5)) --
end as initial_date_time
/* This returns the same result, and due to it not having to do all the converting to/from varchar, will perform much better */
, new_initial_date_time = CONVERT(SMALLDATETIME, DATEADD(mi, DATEPART(mi, initial_date_time) /30 *30, DATEADD(hh, DATEDIFF(hh, 0, initial_date_time), 0)))
from @t
January 30, 2017 at 2:02 pm
Sorry, what the query (which I didn't post the full statement) was about was to count the number of records within the following time slots under the field initial_date_time with a GROUP BY clause. Initial_Date_Time field contains a string in the format of YYYY-mm-dd:
12:00a (so if initial_date_time times were 12:00, 12:04, 12:10), then there are 3 in this 15 mins time slot
12:15a (so if times were 12:15, 12:18, 12:24, 12:29a), then there are 4 records in this slot etc
12:30a
It is so much cleaner using Oracle SQL..
January 30, 2017 at 2:31 pm
Tn37355 - Monday, January 30, 2017 2:02 PMSorry, what the query (which I didn't post the full statement) was about was to count the number of records within the following time slots under the field initial_date_time with a GROUP BY clause. Initial_Date_Time field contains a string in the format of YYYY-mm-dd:12:00a (so if initial_date_time times were 12:00, 12:04, 12:10), then there are 3 in this 15 mins time slot
12:15a (so if times were 12:15, 12:18, 12:24, 12:29a), then there are 4 records in this slot etc
12:30aIt is so much cleaner using Oracle SQL..
Not sure How you get time in a YYYY-mm-dd field, but using any of the SQL datetime data type ....
declare @t as table (
initial_date_time smalldatetime
, SomeData varchar(10)
);
insert into @t values ('2017-01-30 15:00:00', 'xyz')
, ('2017-01-30 15:04:00', 'abc')
, ('2017-01-30 15:10:00', 'qrd')
, ('2017-01-30 15:15:00', 'wth')
, ('2017-01-30 15:18:00', 'juf')
, ('2017-01-30 15:24:00', 'kln')
, ('2017-01-30 15:29:00', 'rfg')
, ('2017-01-30 15:30:00', 'ujm')
, ('2017-01-30 15:37:00', 'pdv')
, ('2017-01-30 15:54:00', 'xch')
SELECT
timeslot = CONVERT(SMALLDATETIME, DATEADD(mi, DATEPART(mi, initial_date_time) /15 *15, DATEADD(hh, DATEDIFF(hh, 0, initial_date_time), 0)))
, Records = COUNT(*)
FROM @t
GROUP BY CONVERT(SMALLDATETIME, DATEADD(mi, DATEPART(mi, initial_date_time) /15 *15, DATEADD(hh, DATEDIFF(hh, 0, initial_date_time), 0)))
January 30, 2017 at 2:32 pm
Tn37355 - Monday, January 30, 2017 2:02 PMSorry, what the query (which I didn't post the full statement) was about was to count the number of records within the following time slots under the field initial_date_time with a GROUP BY clause. Initial_Date_Time field contains a string in the format of YYYY-mm-dd:12:00a (so if initial_date_time times were 12:00, 12:04, 12:10), then there are 3 in this 15 mins time slot
12:15a (so if times were 12:15, 12:18, 12:24, 12:29a), then there are 4 records in this slot etc
12:30aIt is so much cleaner using Oracle SQL..
It's so much cleaner in T-SQL if you use the right approach.
declare @t as table (
initial_date_time smalldatetime
);
insert into @t values('2017-01-30 15:05:00'), ('2017-01-30 15:22:00'), ('2017-01-30 15:37:00'), ('2017-01-30 15:54:00')
SELECT
initial_date_time,
DATEADD(MINUTE, DATEDIFF(MINUTE, '2000-01-01', initial_date_time)/ 15 * 15, '2000-01-01')
from @t
I've used '2000-01-01' as a reference date, but you can use any reference date that doesn't cause an overflow.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 30, 2017 at 2:43 pm
DesNorton - Monday, January 30, 2017 2:31 PMTn37355 - Monday, January 30, 2017 2:02 PMSorry, what the query (which I didn't post the full statement) was about was to count the number of records within the following time slots under the field initial_date_time with a GROUP BY clause. Initial_Date_Time field contains a string in the format of YYYY-mm-dd:12:00a (so if initial_date_time times were 12:00, 12:04, 12:10), then there are 3 in this 15 mins time slot
12:15a (so if times were 12:15, 12:18, 12:24, 12:29a), then there are 4 records in this slot etc
12:30aIt is so much cleaner using Oracle SQL..
Not sure How you get time in a YYYY-mm-dd field, but using any of the SQL datetime data type ....
declare @t as table (
initial_date_time smalldatetime
, SomeData varchar(10)
);insert into @t values ('2017-01-30 15:00:00', 'xyz')
, ('2017-01-30 15:04:00', 'abc')
, ('2017-01-30 15:10:00', 'qrd')
, ('2017-01-30 15:15:00', 'wth')
, ('2017-01-30 15:18:00', 'juf')
, ('2017-01-30 15:24:00', 'kln')
, ('2017-01-30 15:29:00', 'rfg')
, ('2017-01-30 15:30:00', 'ujm')
, ('2017-01-30 15:37:00', 'pdv')
, ('2017-01-30 15:54:00', 'xch')SELECT
timeslot = CONVERT(SMALLDATETIME, DATEADD(mi, DATEPART(mi, initial_date_time) /15 *15, DATEADD(hh, DATEDIFF(hh, 0, initial_date_time), 0)))
, Records = COUNT(*)
FROM @t
GROUP BY CONVERT(SMALLDATETIME, DATEADD(mi, DATEPART(mi, initial_date_time) /15 *15, DATEADD(hh, DATEDIFF(hh, 0, initial_date_time), 0)))
This is still more complicated than it needs to be. You want to calculate the difference using the largest measure that is smaller than the desired granularity. Since the desired granularity is 15 minutes, you want to calculate the difference in minutes, not hours.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 30, 2017 at 9:26 pm
drew.allen - Monday, January 30, 2017 2:43 PMDesNorton - Monday, January 30, 2017 2:31 PMTn37355 - Monday, January 30, 2017 2:02 PMSorry, what the query (which I didn't post the full statement) was about was to count the number of records within the following time slots under the field initial_date_time with a GROUP BY clause. Initial_Date_Time field contains a string in the format of YYYY-mm-dd:12:00a (so if initial_date_time times were 12:00, 12:04, 12:10), then there are 3 in this 15 mins time slot
12:15a (so if times were 12:15, 12:18, 12:24, 12:29a), then there are 4 records in this slot etc
12:30aIt is so much cleaner using Oracle SQL..
Not sure How you get time in a YYYY-mm-dd field, but using any of the SQL datetime data type ....
declare @t as table (
initial_date_time smalldatetime
, SomeData varchar(10)
);insert into @t values ('2017-01-30 15:00:00', 'xyz')
, ('2017-01-30 15:04:00', 'abc')
, ('2017-01-30 15:10:00', 'qrd')
, ('2017-01-30 15:15:00', 'wth')
, ('2017-01-30 15:18:00', 'juf')
, ('2017-01-30 15:24:00', 'kln')
, ('2017-01-30 15:29:00', 'rfg')
, ('2017-01-30 15:30:00', 'ujm')
, ('2017-01-30 15:37:00', 'pdv')
, ('2017-01-30 15:54:00', 'xch')SELECT
timeslot = CONVERT(SMALLDATETIME, DATEADD(mi, DATEPART(mi, initial_date_time) /15 *15, DATEADD(hh, DATEDIFF(hh, 0, initial_date_time), 0)))
, Records = COUNT(*)
FROM @t
GROUP BY CONVERT(SMALLDATETIME, DATEADD(mi, DATEPART(mi, initial_date_time) /15 *15, DATEADD(hh, DATEDIFF(hh, 0, initial_date_time), 0)))This is still more complicated than it needs to be. You want to calculate the difference using the largest measure that is smaller than the desired granularity. Since the desired granularity is 15 minutes, you want to calculate the difference in minutes, not hours.
Drew
Thanks Drew
I must admit, my brain was having a hard time of it last night.
Your solution is much more elegant.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply