Pass through query returns hhnn format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to return a sort time from an SQL stored date via a pass through query. I want this to sort a daily schedule report by appointment start time. In Access I use the query ApptSortStart: Format$([START_TIME],"hhnn") and sort on "hhnn". But I haven't figured out how to do this with SQL. The only way I figured was to return DATEPART(hour, mytime) and DATEPART(minute, mytime) and use two sort criteria in the report, top one for hour and second for minute. However, I am wondering if there is a way to directly return the "hhnn" as a string from the SQL stored date using a SELECT statement in my pass through query.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You might try the CONVERT() SQL'r function:

SELECT CONVERT(CHAR(5), date_col, 8) As HrsMins
FROM TableName

Results:

HrsMins
- -------
09:25
12:30
22:55
17:25

The "style" number in the CONVERT(type, column, style) means output
the column value in hh:nn:ss format. The CHAR(5) means only print the
first 5 chars of the hh:nn:ss format, which is hh:nn.

See the SQL Books on line article on CONVERT function for more info.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDKwLIechKqOuFEgEQJ5bQCfUiPvX/PaKtsTCLIWuK7nwx+J/DEAn2cC
W/XN+Q76ZzSzd+Pmmr6iUFzL
=SJ0G
-----END PGP SIGNATURE-----
 
Back
Top