Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
littlewing
Starting Member
33 Posts |
 Posted - 2004-12-17 : 08:49:31
|
| Hello, I have a table that has two datetime fields. I need a query that returns the first datetime field if that date has not gone by and the second datetime field if the first has gone by. If both have gone by return nothing. Something like:if firstdate > getdate() return firstdateelse if secondate > getdate() return secondate()This will be the AND clause of a SELECT. Would this be better as a UDF?Thanks in advance.LW |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-12-17 : 09:04:27
|
| please give us some sample data, I don't understand what you are looking for. do you want a single date value to be returned, or a set of rows from your table in which they fit the date criteria you have specified?- Jeff |
 |
|
|
dsdeming
479 Posts |
Posted - 2004-12-17 : 09:18:49
|
| I think this will give you what you need.create table #datetest( id int identity( 1,1 ), firstdate datetime, seconddate datetime )insert into #datetest select '1/1/2004', '1/1/2006'insert into #datetest select '1/1/2006', '1/1/2007'insert into #datetest select '1/1/2003', '1/1/2004'select id, returndate = case when getdate() between firstdate and seconddate then seconddate when firstdate > getdate() then firstdate else null endfrom #datetest-- where id = 2drop table #datetestDennis |
|
|
|
littlewing
Starting Member
33 Posts |
Posted - 2004-12-17 : 09:34:35
|
| [code]310145633 2004-12-03 09:00 2004-12-30 11:00200317803 2004-12-11 09:00 2004-12-15 09:00200325866 2004-12-30 14:00 2004-12-31 14:00200440767 2004-12-30 14:00 NULL200443406 2004-12-30 13:00 NULL[/code]Ok sure. I am looking for a set of rows from the table in which they fit the date criteria specified. In the above table then the first record would be returned as the second date value is greater than getdate() even though the first date has passed. The second row would not be returned as both dates have passed. The third row would return as both dates are good - I would want that first date value as my returned date field. The second date value can be null as indicated. Rows four and five would also be returned.I only want two fields returned in my record set - the ID, and "theFirstDateThathasn'tGoneBy".Hope that makes sense, and thank you for the help.LW |
|
|
|
littlewing
Starting Member
33 Posts |
Posted - 2004-12-17 : 10:08:53
|
| [code]310145655 2004-12-03 09:00 2004-12-30 09:00 2004-12-30 09:00200317803 2004-12-30 09:00 NULL 2004-12-30 09:00200325866 2004-12-30 14:00 NULL 2004-12-30 14:00200326376 NULL NULL NULL200428986 NULL NULL NULL200433032 NULL NULL NULL200440767 2004-12-30 14:00 NULL 2004-12-30 14:00200441948 NULL NULL NULL200443406 2004-12-10 13:00 NULL NULL[/code]Dennis, thanks that's real close. The above is the results of my query with your case statement added. I guess I should have mentioned that both fields can be null and I would want to eliminate any record where returndate is null (third column above). I removed your "else null" clause but no effect.Thanks again.LW |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2004-12-17 : 12:09:34
|
| The CASE will always generate a value; even if that value is Null. Remove the unwanted Nulls by putting that criteria into your WHERE clause.HTH=================================================================Happy Holidays! |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2004-12-17 : 12:12:25
|
| The CASE will always generate a value; even if that value is Null. Remove the unwanted Nulls by putting that criteria into your WHERE clause.HTH=================================================================Happy Holidays! |
|
|
|
|
|
|
|
|
one of two dates query