[thelist] SQL/Access

rudy r937 at interlog.com
Tue May 15 00:51:23 CDT 2001

> Is there a way I can get just 9/9 returned and 1:00 PM?

hi daniel

in microsoft access you can use the DatePart function

   select DatePart("m", nflsched.date) as SchedMonth
        , DatePart("d", nflsched.date) as SchedDayOfMonth
        , DatePart("h", nflsched.date) as SchedHour
        , DatePart("n", nflsched.date) as SchedMinute

note it's "n" for minute

the hour and minute come back as integers, so you have to subtract 12 to do
the PM thing and insert zeros for minutes less than 10 yourself --

   IIF(DatePart("h", nflsched.thedate) > 12,
       Str(DatePart("h", nflsched.thedate)-12),
       Str(DatePart("h", nflsched.thedate)) )
 & ":"
 & IIF(DatePart("n", nflsched.thedate) < 10,
       '0' & Str(DatePart("n", nflsched.thedate)),
       Str(DatePart("n", nflsched.thedate)) )
 & IIF(DatePart("h", nflsched.thedate) >= 12,'PM','AM')

unless i've made a booboo that should give you the string you want

i notice you are selecting nflsched.time and nflsched.day as well -- are
these separate fields in the table?  if so, the time would be redundant
with the date field which you say has the time built in, and the day of
week is also redundant, because you can pull that from the date too

    DatePart("w", nflsched.date) as SchedWeekDay

where 1=sunday... 7=saturday

a caution about using column names like "date" and "time" and "day" --
these will work in access, possibly you may have to enclose them in square
brackets, but it's never a good idea to use column names that might be
mistaken for reserved words...


More information about the thelist mailing list