Or COALESCE(Col1, Col2, Col3) is null

It may however fail. You will get a conversion error if you run it on the following table:

create table #test(a uniqueidentifier null, b int null)

But in...

Kevin, I am not sure I understand your first comment, but let me explain in greater detail why I think you should divide by 6 rather than 4.

When you divide numbers 0...

You need to cast to the same datatype to get that working... Something like

select 'member_id' ,  cast(member_id as varchar(100))

from members where member_id = 1

union all

select 'first_name',  first_name

from members where...

We have had the same problem not long ago...

http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=257275

It's not the same. Run your code on the example I posted before

insert into mytable select 1, '20050101 5:59', 0

insert into mytable select 1, '20050101 11:58', 0

insert into mytable select...

'2004-02-21' returns 2 years 0 months 1 day and I would expect it to return 2 years, 0 months, 0 days. Similarly, '2006-01-21' returns 1 month, 1 day and I...

Please post table definition, sample data and expected output.

Kevin, I don't agree. Enter the test data below and run my code - you will see that myflag is set to 1 for 5:59 and 12:00. If you remove...

This doesn't work: '20060131' returns 0 years, 2 months, 18 days (today is '20060221'), while the day after, '20060201', correctly returns 0 years, 0 months, 20 days.

My suggestion may still be...

Previous iteration of the loop.

This can probably be obtained in may ways. You could try the statement below:

select isnull(t.FormId, dt.FormId)

from

(select 1 as FormId) dt

left join Tablename t

on t.Action in (1,2,3,4) and t.Status = 'Y'

You could also try this:

declare @days int

select @days =

case when datepart(d, @date1) <= datepart(d, @date2)

then

datepart(d, @date2) - datepart(d, @date1)

else

datediff(d, @date1 - datepart(d, @date1) + 1, dateadd(m, 1, @date1 - datepart(d,...

If you insist on solving this, it is probably better to loop over time than over each row in the table

declare @time datetime

select @time = '20050101' -- Your starting point

while...

Yes, the first one wasn't correct for dates after '20060213', as Jeff Moden pointed out. My present formula isn't correct for dates before '19000101', but I guess that's all right...

I understand what you are saying. I cannot read from John's post whether he wants your example to count as a swipe during each period or whether he wants to...