Erik Porter's Blog

Life and Development at Microsoft and Other Technology Discussions

News

    Separating Date and Time in T-SQL

    This usually isn't necessary, but I'm setting up some Views for a customer doing some reports in Access and the customer needed two fields, one with just the Date and another with just the Time.  At first search on Google, it came up with using the DATEPART Function to get each part of the date or time and concatinating those parts together to get what I wanted.  Not only was that pretty disgusting, it didn't pad the minutes or seconds (couldn't find an easy way to pad them myself either).  So, after doing some more searching, I found that CONVERT can take a 3rd optional parameter of style.  Unfortunately you can't do your own custom styles, but right off the bad it takes care of the date and doing some extra work I can get the time format I want like this...

    SELECT
         CONVERT(char(10), MyDateTime, 101) AS [Date],
         CONVERT(varchar(2),
              CASE
                   WHEN DATEPART([hour], MyDateTime) > 12 THEN CONVERT(varchar(2), (DATEPART([hour], MyDateTime) - 12))
                   WHEN DATEPART([hour], MyDateTime) = 0 THEN '12'
                   ELSE CONVERT(varchar(2), DATEPART([hour], MyDateTime))
              END
         ) + ':' +
         CONVERT(char(2), SUBSTRING(CONVERT(char(5), MyDateTime, 108), 4, 2)) + ' ' +
         CONVERT(varchar(2),
              CASE
                   WHEN DATEPART([hour], MyDateTime) > 12 THEN 'PM'
                   ELSE 'AM'
              END
         ) AS [Time]
    FROM
         MyTable

    I'm not saying this is the best way, just something I did that seems to work!  :)  I'm more posting this for myself when I forget how to do it later, but maybe it will help someone as well (this is the first time I've ever needed to do this).

    Posted: Apr 28 2004, 04:24 PM by HumanCompiler | with 16 comment(s)
    Filed under: ,

    Comments

    Jerry Pisk said:

    Say goodbye to localization as you're passing date values as strings formatted with your SQL Server's locale.

    The datetime data type is stored as a double internally, to get the date alone you just need to strip the fractional part, to get the time portion you strip the integer part. Pass it back to the client as datetime and format according to their locale, not your database server's.
    # April 28, 2004 7:36 PM

    HumanCompiler said:

    That's fine...doesn't affect us at all (this is for one client doing access reports). For those of you who would use this and it would affect you...there's your warning from Jerry! ;)
    # April 28, 2004 7:39 PM

    TrackBack said:

    Take Outs for 28 April 2004
    # April 29, 2004 3:28 AM

    Clarens said:

    why not have the date and time seperated right from the start of the tabel design before inputing the data!!!
    # May 29, 2004 6:03 PM

    HumanCompiler said:

    Well, for our case, that wasn't option, the table was already designed.
    # May 30, 2004 2:36 PM

    Luciano Evaristo Guerche said:

    Reading Erik Porter's post Separating Date and Time in T-SQL, I remembered I have created some SQL

    # August 25, 2006 3:58 AM

    notes » Blog Archive » Dealing with t-sql DATE and TIME said:

    PingBack from http://david.egerton.info/?p=80

    # December 11, 2006 1:24 PM

    notes » Blog Archive » Dealing with t-sql DATE and TIME said:

    PingBack from http://david.egerton.info/?p=81

    # December 11, 2006 1:28 PM

    mk said:

    Thanks for the tip! This posting saved me a bunch of time.

    # February 6, 2007 11:54 AM

    jc said:

    code error: 12pm is showing as 12am

    # January 4, 2008 4:51 PM

    jpl said:

    to fix the error with 12pm showing up as 12am, change the last 12 in the code to an 11.  (or change the last 12 > to 12 >= )

    But good code anyway.  Thanks!

    # March 15, 2008 6:21 PM

    Tom said:

    RIGHT ('0' + CAST(DATEPART(hh, GetDate()) AS varchar(255)) + ':' + CAST(DATEPART(n, GetDate()) AS varchar(255)), 5)

    Got me what I needed.

    # August 1, 2008 5:28 AM

    Jana said:

    Very good code. Saved lot of time

    # September 2, 2008 6:46 PM

    Antony Koch said:

    select  right('0'+cast(datepart(HH, getdate()) as nvarchar(2)),2)+':'+right('0'+cast(datepart(mm, getdate()) as nvarchar(2)),2)+':'+right('0'+cast(datepart(ss,getdate()) as nvarchar(2)),2)

    # October 13, 2008 12:09 PM

    Luis Fernando said:

    CAST(FLOOR(CAST(@DataAtual AS float)) AS datetime)

    # November 12, 2008 6:16 AM

    Genious said:

    can some one please list simple 1 line code to get time stamp in t-sql.

    i had these functions but lost the page.

    any help here!

    Regards,

    Genious

    # November 27, 2008 4:02 AM