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
Our last winter opening until May! Just 409/nt for this gorgeous OCEANFRONT condo!
Mar 18-24
Are you craving PANORAMIC views from your condo & private balcony? Condo B9 boasts unobstructed 180° up-close-&-personal views of white sand...
- 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
Feb 28-Mar 4 or Mar 20-26 - just $399/nt! Extend your current trip & see what being oceanfront is all about!
We can't wait to welcome your party of 4 to Kihei Sands Beachfront Condominiums B8 in sunny South Maui! Condo B8 is a 2-bedroom, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Gap Fills:
*6 nights Mar 30-Apr 5 $309 Our last week this season - book now for whale season before it's gone!
Fabulous views - steps from the beach - oceanfront property!
Welcome to Kihei Sands Beachfront Condominiums Condo A11 ...
- 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 min:
Feb 25-26-27 - $349
Mar 9&10 - $209/nt
Apr 1-5 Four nights - ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
LAST MINUTE DEALS JUST $349/night:
Mar 8-14
Mar 22-25
APRIL just $399/night - book now before dates disappear for good!
This HIGH-END five-star luxury condo's impeccable remodel was finished in August 2024 with your comfort...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Last minute Gap Fill Mar 3-6! BOOK NOW BEFORE IT's GONE!
We are ON THE WATER! You won't need to cross the street or walk through acres of land to get your feet wet. It's 75, sunny & the drinks are cold! If you're looking for an ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
7-night min:
$339/nt - March 22 - 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? ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kihei
Incredible views!
Gap Fills:
Feb 25-26-27 $229/nt
March 22-23-24 $229/nt
Apr 9-15 Six nights $389
You'll love staying in our one-bedroom Condo A10 with a gorgeous view of the beach & the Pacific Ocean. The bedroom boasts a...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Our condo is so popular we only have one week left this winter! Book now before it's gone!
Mar 19-27 $339/nt Out last availability and last chance to see whales this season.
Are you ready to come out of the dark & into the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
GAP FILLS - 1 night minimum
Feb 25-Mar 3 - $329/nt
Mar 13-14-15 - $329/nt
All of April - start of low season - just $319/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 29-Apr 3 - Need an extra night or two? Just $359/nt in our gorgeous condo on oceanfront property! Steps to the beach!
Maui has never been more affordable! Condo B4 is a beautifully renovated 2B/2Ba townhouse-style unit ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
REDUCED LAST MINUTE AVAILABILITY - DO NOT PASS THIS UP!
*Today - Mar 3 - $209/night! Superior Views!
Mar 11 & 12 - $225/nt
Welcome to Condo B10 in sunny South Maui! This second-story (no elevator) condo is perfect for two couples ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Gap Fill:
Mar 5-6-7 $299/nt during 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 unpopulated spot within a 15-minute drive of dining, shopping &...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Whales have been RIGHT IN FRONT of our building - don't miss this! They head back to Alaska mid-April - last chance to see them!
Feb 24-Mar 8 $389nt - 5 Night Min.
Gap Fill: Mar 22 & 23 - $339/nt OCEANFRONT
Condo A8 is a ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Thinking of extending your current trip while it's 75 & sunny? We have great prices on Gap Fills with one night mins:
2/22-25 just $299/nt
3/21-25 just $299/nt
Family reunion, birthday or anniversary on the horizon? Have an ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Do you need just a few more nights in March? One night min:
March 7-13 @ $349/nt
Gap Fill: Mar 20 & 21 $299/nt
Winter is WHALE SEASON - if you've never been to Maui to see the whales, now's the time to check this off your bucket ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Gap Fill: Feb 27-Mar 3 - $319/nt
Mar 21-30 $319/nt - any 7 nights (min.)
Tired of waiting for Spring? Maui is waiting for YOU! Everyone will stay COOL with whisper-quiet Mitsubishi split A/C in the living area & each bedroom. Ceiling ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Do you need just a few nights longer in Maui? Check out our Gap Fills - 1 Night min @ $369/nt:
2/28-3/4
3/10-13
3/30-4/4
This condo is priced for 4 persons - we'll add $42/pp/night for each additional guest over 4, up to 6 ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Are you ready to shake off the winter chill? Come join us for sunny days and warm evenings at low season rates!
Our 2-bedroom/2-bath townhouse style home with ocean view is on the 2nd level (no elevator) at Kihei Sands Condominiums. It is...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Just $419/night through June 15! Friends trip? Kids & grandparents? Come stay with us!
Aloha & welcome to Condo B15, a 4-BEDS / 2-BATHS townhouse-style home on the 2nd level (no elevator) at Kihei Sands Condominiums!
This ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
OCEANFRONT PROPERTY! LAST MINUTE AVAILABILITY - REDUCED! 5 night min
LITERALLY A FEW STEPS FROM THE BEACH AND OCEAN!
Mar 2-Mar 9 $199/nt - Last chance to see whales this winter! Book now - this deal won't last!
You'll love ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
