"Slipt" rows based on datetime

Hello!

I have a table that, among other columns, has two datetime columns which indicate the initial and the final time. This would be an exemple of data in this table:

row1:

initial_time: 2006-05-24 8:00:00

final_time: 2006-05-24 8:30:00

row2:

initial_time: 2006-05-24 8:35:00

final_time: 2006-05-24 9:15:00

I would like to split a row in two new rows if final time's hour is different of initial time's hour, so I would like to split row2 into:

row2_a:

initial_time: 2006-05-24 8:35:00

initial_time: 2006-05-24 8:59:59

row2_b:

initial_time: 2006-05-24 9:00:00

initial_time: 2006-05-24 9:15:00

Is it possible to do it in a query, I mean, without using procedures?

Thank you!

? It gets a bit complex, but here's one way: create table #table ( initial_time datetime, final_time datetime) insert #table values( '2006-05-24 8:00:00', '2006-05-24 8:30:00') insert #table values( '2006-05-24 8:35:00', '2006-05-24 9:15:00') SELECT CASE x.N WHEN 0 THEN T.initial_time WHEN 1 THEN DATEADD(ms, ((DATEPART(minute, T.final_time) * 60000) + (DATEPART(second, T.final_time) * 1000) + DATEPART(millisecond, T.final_time)) * -1, T.final_time) END AS initial_time, CASE x.N WHEN 0 THEN CASE DATEDIFF(hour, T.initial_time, T.final_time) WHEN 0 THEN T.final_time WHEN 1 THEN DATEADD(millisecond, -3, DATEADD(millisecond, ((DATEPART(minute, T.final_time) * 60000) + (DATEPART(second, T.final_time) * 1000) + DATEPART(millisecond, T.final_time)) * -1, T.final_time)) END WHEN 1 THEN T.final_time END AS final_timeFROM #table TJOIN( SELECT 0 UNION ALL SELECT 1) x (N) ON x.N <= DATEDIFF(hour, T.initial_time, T.final_time) drop table #tablego -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <AnaC@.discussions.microsoft..com> wrote in message news:bdb330d8-5a34-409c-aaf3-57044628fc13@.discussions.microsoft.com... Hello! I have a table that, among other columns, has two datetime columns which indicate the initial and the final time. This would be an exemple of data in this table: row1: initial_time: 2006-05-24 8:00:00 final_time: 2006-05-24 8:30:00 row2: initial_time: 2006-05-24 8:35:00 final_time: 2006-05-24 9:15:00 I would like to split a row in two new rows if final time's hour is different of initial time's hour, so I would like to split row2 into: row2_a: initial_time: 2006-05-24 8:35:00 initial_time: 2006-05-24 8:59:59 row2_b: initial_time: 2006-05-24 9:00:00 initial_time: 2006-05-24 9:15:00 Is it possible to do it in a query, I mean, without using procedures? Thank you!|||Thank you!

& in the where clause

Hello,
SELECT discounts.*, discounttype AS Expr1
FROM discounts
WHERE (discounttype = 'Initial & Customer')
This query in pub database does not bring anything because of ‘&’ in where
clause. How can I get this working without removing & in the table?
Thanks,
Jim.
not sure, why do you want to do that. but if you are using "=" equal
operator for comparison then query will look for exact match in the table.
If exact match is not found then you will not retrieve any row.
either change source data or change the value that is being passed in where
clause.
you may try something as follows:
SELECT discounts.*, discounttype AS Expr1
FROM discounts
WHERE replace(discounttype, ' ', ' & ') = 'Initial & Customer'
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||pubs.dbo.discounts has a row where discounttype = 'Initial Customer' If
you want the row because it contains Initial and Customer, then you can
try something like
select ...
from discounts
where discounttype like '%Initial%' and discounttype like '%Customer'
I'm not sure what the point of the string 'Initial & Customer' is.
SK
JIM.H. wrote:

>Hello,
>SELECT discounts.*, discounttype AS Expr1
>FROM discounts
>WHERE (discounttype = 'Initial & Customer')
>This query in pub database does not bring anything because of ‘&’ in where
>clause. How can I get this working without removing & in the table?
>Thanks,
>Jim.
>
>