select * from (Select a.`postalCode`,a.`uniturl`, `region`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `source`, `property_type`, `address`,round(price) as price,minimum_nights, `rating`, `review_count`,`latitude`, `longitude`,case when propertyName='' then trim(address) else CONCAT(CONCAT(propertyName,'(',trim(address)),'',')') end as addre,propertyName,
(6371 *
acos(
cos( radians( 21.2759167 ) ) *
cos( radians( `latitude` ) ) *
cos(
radians( `longitude` ) - radians( -157.8233677 )
) +
sin(radians(21.2759167)) *
sin(radians(`latitude`))
)
) as distance from `vacationrentalindex` a inner join vacationrentalindexfeed b on a.uniturl=b.unitUrl and a.headline!='' and a.thumbnailUrl!='' and b.enabled=1 and b.blocked=0 ) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%Kihei Sands%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Gap Fill:
Feb 15-16-17
Our last winter openings until May - 3-night minimum @ $459/nt
Jan 4-11
Jan 18-25
Mar 18-24
Are you craving PANORAMIC views from your condo & private balcony? Condo B9 boasts unobstructed 180° ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Beautifully Renovated to the studs BEACHFRONT ground floor 1 Bedroom/1 Bath Condo at Kihei Sands (Sleeps 2 Max).
There’s something about the salty air and lull of the tide that encompasses the charm of Coastal Beach living. The condo...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Jan 4-12 and Jan 19-29 are our last week-long dates available for winter - book our oceanfront condo NOW for WHALE SEASON!
Feb 28-Mar4 $459/nt
Mar 20-26 $459.nt
We can't wait to welcome your party of 4 to Kihei Sands Beachfront ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Gap Fills:
*2 nights Jan 1 & 2 $209/nt
*6 nights Mar 30-Apr 5 $309
Feb 12-28 now available! 7-night minimum at just $399/nt - Book now for whale season before it's gone!
Fabulous views - steps from the beach - oceanfront ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Whales have been RIGHT IN FRONT of our building - don't miss this!
Are you island hopping and need just a few nights in Maui? Fill our gaps!
One night minimum:
Jan 4-9 Five nights - $199/nt
Jan 22-26 Four nights - $199/nt
Feb ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Jan 5-8 - 1 nt min $299
Jan 14-20 - 1 nt min - $299
Jan 30-Feb 1 - 1 nt min - $299
Mar 8-14 - 2 nt min - $449
Mar 22+ - 7 nt min - $449
Winter is WHALE SEASON! This HIGH-END five-star luxury condo's impeccable remodel was ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Gap Fills - Need an extra night or two?
Feb 1&2 $189/nt
Feb 25&26 $189/nt
Is Maui on your mind? The jacaranda trees start blooming in April, along with many other flowering trees. You've never seen anything like it when everything...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
7-night minimum:
March 13 - Apr 1
Dates JUST opened up! Whales have been RIGHT IN FRONT of our building - book now before they head back to Alaska mid-April!
ALOHA! Are you ready to spend some time barefoot at the beach? Let us ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kihei
Gap Fills:
Jan 11-12-13 $229/nt
Feb 25-26-27 $229/nt
March 22-26 Four nights $229/nt
Apr 9-15 Six nights $389
Jan 24-Feb 9 - Min 7 nights - $389/nt - Our last full weeks this winter! - Book NOW!
You'll love staying...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Our condo is so popular we only have a few weeks left this winter! Book now before they're gone!
Gap Fill - Jan 12-16- $229/nt - one nt minimum
Mar 19-Apr 15 - any 7 nights for $339/nt
Are you ready to come out of the dark & ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
GAP FILLS - 1 night minimum
Jan 17&18 $199/nt
Feb 6-11 - $369/nt
Feb 25-Apr 3 - $369/nt
Mar 13-14-15 - $349/nt
Aloha & welcome to Condo B14! This 2nd floor (no elevator) condo is priced for 4 persons - we'll add ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Our property is NOT impacted by the news of potentially prohibiting Short Term Rentals (STR) in Maui. Kihei Sands is zoned as a hotel property so meaning it is exempt from any of the potential STR regulations being proposed by the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Gap Fill:
Mar 3 & 4 - Need an extra night or two? Just $259/nt
Maui has never been more affordable! Condo B4 is a beautifully renovated 2B/2Ba townhouse-style unit located on the second floor (no elevator.) Remodeled with the highest ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
*Jan 4-9 -- $349/nt - 1 nt min
*Feb 7-8-9 -- $399/nt - 1 nt min
*Feb 18-28 - $469 - 7-night min
*March 8-22 - $429 - 7-night min
Welcome to Condo B10 in sunny South Maui! This second-story (no elevator) condo is perfect for two ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Gap Fills:
Dec 22-27 - $199/nt
Mar 5-9 - $329/nt
Any 7 Nights:
Feb 2-11 $359/nt -- whale season - our most popular month of the year!
The weather is warm, skies are sunny & the drinks are cold! If you're looking for an ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Whales have been RIGHT IN FRONT of our building - don't miss this!
Our whales head back to Alaska mid-April - book now!
7-night min.
Jan 16-23 $299/nt
Feb 1-9 $429/nt
Feb 17-Mar 8 $429/nt
Mar 22-Apr 15 $429/nt
Condo ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Avail Dates: Feb 22-23-24 $399/nt
January is WHALE SEASON! ALL MONTH at $349 - you do not want to miss this!
Mar 4-15 $389/nt
Family reunion, birthday or anniversary on the horizon? Have an unforgettable family trip together - no ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
January 4-15 $349/nt - Looking for an after-Christmas trip? This one's perfect for two couples to split. Act now - this condo is POPULAR!
March 7-22 $359/nt - WHALE SEASON - if you've never been to Maui to see the whales, now's the time to ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
GAP FILLS:
Dec 19-20-21 $189/nt - 1 nt min.
Jan 24-25 - $189/nt - 1 nt min.
Feb 10-11-12 $379/nt - 1 nt min.
Winter Weeks Avail:
Feb 20-Mar 3 - $419/nt - 7 nt min. Book now before they're all gone!
Mar13-30 $389/nt - 7 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Gap Fills
Jan 29-31 - $309/nt
7-nt minimum
Jan 7-15 - $349/nt Book now for whale season! You don't want to miss this!
Feb 27-Mar15 - $369/nt - Last chance to see the whales in 2025 before they head back to Alaska.
This ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Gap Fills $199/nt - 1 nt min.
Jan 3-4-5
Feb 23-24
Mar 4-5
Our next available week-long dates are March 20 at $379 - our most popular month of the year! Whale season is the BEST - book now before they're gone!
Our ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Aloha & welcome to Condo B15, a 4-BEDS / 2-BATHS townhouse-style home on the 2nd level (no elevator) at Kihei Sands Condominiums!
This welcoming, comfortable condo is decorated with a TROPICAL feel & is ready to be your vacation home away...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Gap Fills - 1 night min:
Jan 1&2 - $229/nt
7 night minimum:
Feb 22-Mar 9 $389/nt - Last chance to see whales this winter! When it's gone, it's gone!
You'll love staying in our home-away-from-home. If you're looking for a ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
