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 |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2007-11-20 : 04:31:01
|
| I can get min and max dates from a date field in a table but how do I get the date which is in between these two?For exampleI getselect min(ImportDate), max(ImportDate)Now I would like to get the ImportDate that falls in the middle of these two dates.There may be several ImportDates and I would like to get three records i.e. min, middle, maxThanks |
|
|
hitman
Starting Member
23 Posts |
Posted - 2007-11-20 : 04:38:33
|
| so you need date in the middle of this interval? doesn't matter if it's in data or not?-----------------------http://www.sqltips.info----------------------- |
 |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2007-11-20 : 04:51:56
|
quote:
Originally posted by hitman
so you need date in the middle of this interval? doesn't matter if it's in data or not?-----------------------http://www.sqltips.info-----------------------
The field is in date datatype, so it will be date,so not sure what you mean by "doesn't matter if it's in data or not?"Thanks |
|
|
|
hitman
Starting Member
23 Posts |
Posted - 2007-11-20 : 04:55:28
|
| I thought if something like this dateadd(ss, datediff(ss, min(ImportDate), max(ImportDate)) / 2, min(ImportDate))is ok?-----------------------http://www.sqltips.info----------------------- |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-20 : 05:06:00
|
| What if you have data such as a min date of 18th Nov 2007 and a max date of 20th Nov 2007, but no records for the 19th in your dataset? |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2007-11-20 : 05:11:22
|
quote:
Originally posted by RickD
What if you have data such as a min date of 18th Nov 2007 and a max date of 20th Nov 2007, but no records for the 19th in your dataset?
That's true,I guess either set the middle to be the same as min or max.Thanks |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-20 : 05:23:11
|
| in this case you would need to do an IF EXISTS on the above query and then set the value accordingly. |
|
|
|
|
|
|
|
|
MID(Date)