DatePart

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Portroe - 17 Feb 2004 14:34 GMT

Hi All,

I am currently struggling with the use of the DatePart() function,

for instance if I have a check in and check out date in my hotel
datebase, how can I find all bookings I have for may,

A pointer to an online tutorial would be appreciated,

thanks

Portroe

Reply to this Message

mountain man - 17 Feb 2004 21:23 GMT

> I am currently struggling with the use of the DatePart() function,
> for instance if I have a check in and check out date in my hotel
> datebase, how can I find all bookings I have for may,

select count(*)
from bookings b
where datepart(mm, b.bookingdate)=5

> A pointer to an online tutorial would be appreciated,

There is info in the Query Analyser Help file, part
of the SQL Books OnLine.

Pete Brown
Falls Creek
Oz

Reply to this Message

Bob Hairgrove - 19 Feb 2004 11:17 GMT

>Hi All,
>
[quoted text clipped - 4 lines]
>
>A pointer to an online tutorial would be appreciated,

Sounds more like a MSAccess/VBA question to me. The Month(<some date>)
function would be easier to use here, but it's also VBA and therefore
does not work for anything but queries executing within Access itself,
as opposed to queries over an ODBC connections to an Access database,
or ASP web pages, for example.

If you store check-in and check-out dates in separate columns for the
same booking, then you can use "BETWEEN ... AND" syntax which is also
portable ANSI-SQL syntax. This would give you a booking for a stay
which is entirely within the month of May. However, you will typically
have some bookings which start on April 30th and end on May 2, for
example, or cross the end of the month from May to June. Also, there
is the (remote?) possibility that someone might stay for more than a
month.

To catch all these, your "where" clause might look like this:

WHERE (checkin BETWEEN #05/01/2003# AND #05/31/2003#)
OR (checkin <= #05/01/2003# AND checkout >= #05/01/2003#)
OR (checkin <= #05/31/2003# AND checkout >= #05/31/2003#)
etc.

You need the parentheses when mixing "AND" and "OR", BTW.

The date syntax as written above is the way Access understands it. If
you are connecting from ODBC, you might need to use the ODBC canonical
date syntax "{d '2003-05-01'}" for May 1st, for example (check the
ODBC Programmer's Reference in MSDN for the exact syntax ... I might
have misplaced a quote or something). ODBC also has some functions
which you may be able to use which work similar to the VBA functions
in Access.

HTH
--
Bob Hairgrove
NoSpamPlease@Home.com

Reply to this Message

Bob Badour - 19 Feb 2004 15:47 GMT

> >Hi All,
> >
[quoted text clipped - 26 lines]
> OR (checkin <= #05/31/2003# AND checkout >= #05/31/2003#)
> etc.

Overlaps is more concisely and recognizably expressed as A starts before B
ends and ends after B starts. ie:

checkin <= #05/31/2003# and checkout >= #05/01/2003#

Reply to this Message

Jarl Hermansson - 19 Feb 2004 21:52 GMT

> Overlaps is more concisely and recognizably expressed as A starts before B
> ends and ends after B starts. ie:
>
> checkin <= #05/31/2003# and checkout >= #05/01/2003#

Note that SQL-99 specifies OVERLAPS in a kind of unintuitive way,
since start dates and end dates are treated differently:

(S1 > S2 AND NOT (S1 >= T2 AND T1 >= T2))
OR
(S2 > S1 AND NOT (S2 >= T1 AND T2 >= T1))
OR
(S1 = S2 AND (T1 <> T2 OR T1 = T2))

This means that if two periods have just one single day in common,
they overlap as long as this day is not the ending day of one of the
periods.

Example same start date:
SQL>select case when (date '2004-02-01', date '2004-02-02')
SQL& overlaps (date '2004-02-01', date '2004-02-01') then 1
SQL& else 0 end from onerow_table;

==
1

1 row found

Example same end date:
SQL>select case when (date '2004-02-01', date '2004-02-02')
SQL& overlaps (date '2004-02-02', date '2004-02-02') then 1
SQL& else 0 end from onerow_table;

==
0

1 row found

Regards,
Jarl

Reply to this Message

Bob Badour - 19 Feb 2004 23:13 GMT

> > Overlaps is more concisely and recognizably expressed as A starts before B
> > ends and ends after B starts. ie:
[quoted text clipped - 33 lines]
>
> 1 row found

SQL-99 specifies nothing of worth or value.

Reply to this Message

mountain man - 20 Feb 2004 03:24 GMT

> > >Hi All,
> > >
[quoted text clipped - 31 lines]
>
> checkin <= #05/31/2003# and checkout >= #05/01/2003#

What language is this?

The original post specified the *datepart()* function.
This is a standard function in SQL server.

It requires nothing but the following:
DATEPART (datepart, date)

In the above instance the record selection qualifier
simply becomes:

where DATEPART (mm, checkin_date) = 5
(mm being month)

Samples available in ms Book OnLine.

Reply to this Message

Bob Hairgrove - 20 Feb 2004 13:38 GMT

>What language is this?

MSAccess also has a DatePart() function. However, it's not portable
(neither is the date syntax #...#).

>The original post specified the *datepart()* function.
>This is a standard function in SQL server.

So maybe not everyone uses SQL Server, either. I tried to suggest a
solution which (except for the #...#) would work in other environments
as well.

--
Bob Hairgrove
NoSpamPlease@Home.com

Reply to this Message

mountain man - 21 Feb 2004 04:55 GMT

> >What language is this?
>
> MSAccess also has a DatePart() function. However, it's not portable
> (neither is the date syntax #...#).

> >The original post specified the *datepart()* function.
> >This is a standard function in SQL server.
>
> So maybe not everyone uses SQL Server, either. I tried to suggest a
> solution which (except for the #...#) would work in other environments
> as well.

The generalist approach has merit, I was just not sure
whether in fact it was a "proprietory database language"
that you used, or whether some specific SQL form.

The collective responses time and time again highlight
the fact that the task can often be more-than-comfortably
done by a number of disparate methods.

Pete Brown

Reply to this Message