//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
      COM / ActiveX Components
      Forms
      General Topics
      Date/Time Routines
      Email Scripts & Info
      Files/Directories & FSO
   Databases
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
classicasp.aspfaq.com

ASP FAQ Tutorials :: Classic ASP 1.0 :: Date/Time Routines :: How do I select time only from a DATETIME column?


How do I select time only from a DATETIME column?

You have two options that come to mind straight away. 
 
One is to use the CONVERT function in conjunction with specific style numbers, to convert the date value into a specific type of string. I like using 114, because you can simply change the number of characters returned to increase accuracy. For example, for HH:MM
 
SELECT 
    CONVERT(CHAR(5), DateColumn, 114) 
FROM 
    table 
[WHERE ... ]
 
Will produce the following: 
 
11:45
 
For HH:MM:SS
 
SELECT 
    CONVERT(CHAR(8), DateColumn, 114) 
FROM 
    table 
[WHERE ... ]
 
Will produce the following: 
 
11:45:37
 
And for HH:MM:SS:mmm
 
SELECT 
    CONVERT(CHAR(12), DateColumn, 114) 
FROM 
    table 
[WHERE ... ]
 
Will produce the following: 
 
11:45:37:623
 
Depending on your application, you may need any of the above accuracies. Usually, though, to-the-minute is sufficient. 
 

The other option is to use DATEPART to concatenate the time yourself. This is a bit messier, but is useful, for example, if you only want the minutes and are not concerned about the actual hour. I can't think of a practical use for this, but one must exist. 
 
SELECT 
    DATEPART(MINUTE, DateColumn) 
FROM 
    table 
[WHERE ... ]
 
If you want to build an entire time string on your own, you could do this: 
 
SELECT 
    CONVERT(VARCHAR(2),DATEPART(HOUR, DateColumn)) + ':' + 
    CONVERT(VARCHAR(2),DATEPART(MINUTE, DateColumn)) 
FROM 
    table 
[WHERE ... ]
 
Now, you might find that minutes less than 10 will result in weird padding; for example, if you were going to use this technique for building a time string on your own, you could end up with a result as follows: 
 
5:3
 
This would be 5:03, but could obviously be misconstrued by the user. Here is how I work around this scenario: 
 
SELECT 
    CONVERT(VARCHAR(2), DATEPART(HOUR, DateColumn)) + ':' + 
    CASE 
        WHEN DATEPART(MINUTE, DateColumn) < 10 THEN 
            '0'+CONVERT(VARCHAR(2),DATEPART(MINUTE, DateColumn)) 
        ELSE 
            CONVERT(VARCHAR(2),DATEPART(MINUTE, DateColumn)) 
    END 
FROM 
    table 
[WHERE ...]

Related Articles

Can I get millisecond accuracy in ASP?
Can I make VBScript format dates for me?
Could I get a little help with dates?
Given a date, how do I find the beginning and end of that week?
Given two dates, how do I determine an age?
How do I calculate dates, such as the first day of the month?
How do I convert a DATEDIFF to days, hours, and minutes?
How do I convert a timespan, in seconds, to HH:MM:SS?
How do I convert local time to UTC (GMT) time?
How do I count the number of business days between two dates?
How do I delimit/format dates for database entry?
How do I determine the number of seconds since 1/1/1970?
How do I display time in military format?
How do I implement a calendar / datepicker in ASP?
Why do I have problems inserting NOW() into a database?
Why does JavaScript's document.lastModified() not work in ASP files?

 

 


Created: 9/17/2000 | Last Updated: 6/16/2003 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (358)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...