Member Avatar for mangel.murti

Hi ,all


i have two field one is checkin date and other is checkout date .
i have table structure as i attached jpg file here. when user enter dates i have to show his season on the basis of hotel id?

note:user can enter any date checkin date and checkout date


any help appre...

thanx

jh.JPG 37.08 KB
  • 2 Contributors
  • 3 Replies
  • 84 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by smantscheff

Dani AI

Generated

Short answer: pick seasons that overlap the booking interval, not only seasons that completely contain it. was testing a containment condition (season must start on/before check‑in and end on/after check‑out) — that will miss stays that span two or more seasons. was right to ask for the table DDL and sample rows: wrong column types or formats (VARCHAR, unix seconds, different date format) will make comparisons fail.

A safe pattern is to filter by hotel and require an interval overlap (season_start <= booking_checkout AND season_end >= booking_checkin). For splitting the stay into per‑season nights use GREATEST/LEAST to clip the overlap and DATEDIFF to count days. Example pattern:

SELECT SeasonID, SeasonName,
       GREATEST(startdate, :checkin) AS overlap_start,
       LEAST(enddate,   :checkout) AS overlap_end,
       DATEDIFF(LEAST(enddate, :checkout), GREATEST(startdate, :checkin)) AS nights
FROM tblSeason
WHERE HotelID = :hotelId
  AND startdate <= :checkout
  AND enddate   >= :checkin;

Notes and cautions: ensure startdate/enddate are DATE or DATETIME (not text) or convert them with STR_TO_DATE/FROM_UNIXTIME as needed. Decide whether checkout is exclusive (typical) or inclusive and adjust the DATEDIFF calculation accordingly. If seasons are defined without years or wrap new‑year (e.g., Dec→Jan), normalize them into concrete ranges for the booking year(s). For MySQL reference on date types and functions, see the official docs: Date and Time Types and Date/Time functions (DATEDIFF) plus comparison helpers.

Recommended Answers

All 3 Replies

Member Avatar for smantscheff

What did you try already that did not work for you?

Member Avatar for mangel.murti

What did you try already that did not work for you?

SELECT SeasonID,SeasonName FROM tblSeason 
where HotelID='14' and (startdate<= '2010/10/01') and (enddate >= '2010/12/31')

i try using this query but not getting write output based on checkin checkout.

Member Avatar for smantscheff

Please show the table structure (in form of a CREATE TABLE statement). Maybe your datetime fields are in unix seconds format? Also please show the row with the HotelID 14 which should match your query.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.