Post reply

Stripping off the time

  • July 9, 2012 at 6:37 am

    #260901

    How do I remove the time portion from my date/time results?

    SELECT CAST(FLOOR(CAST(EecDateOfLastHire AS FLOAT)) AS DATETIME) from empcomp

    gives me:

    1984-08-13 00:00:00.000

    when what I want is:

    1984-08-13

  • July 9, 2012 at 6:44 am

    #1510302

    This will do the trick

    DECLARE @d DATETIME = '1984-08-13 00:00:00.000'

    SELECT CONVERT(NVARCHAR(10),@d,120)

  • July 9, 2012 at 7:03 am

    #1510314

    prefect thanks!

  • July 9, 2012 at 7:18 am

    #1510329

    krypto69 (7/9/2012)

    How do I remove the time portion from my date/time results?

    SELECT CAST(FLOOR(CAST(EecDateOfLastHire AS FLOAT)) AS DATETIME) from empcomp

    gives me:

    1984-08-13 00:00:00.000

    when what I want is:

    1984-08-13

    It's not possible without changing the datatype. If you're ok with that, then this will do the trick:

    SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(),102),'.','-')

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • July 9, 2012 at 2:42 pm

    #1510595

    krypto69 (7/9/2012)

    when what I want is:

    1984-08-13

    is this for reporting purposes ...or are you expecting to perform further calculations on the result?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • July 10, 2012 at 9:33 am

    #1511038

    anthony.green (7/9/2012)

    This will do the trick

    DECLARE @d DATETIME = '1984-08-13 00:00:00.000'

    SELECT CONVERT(NVARCHAR(10),@d,120)

    Why are you converting to Nvarchar?? 2 bytes of storage for nothing there, isn't it?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • July 11, 2012 at 12:51 am

    #1511351

    TheSQLGuru (7/10/2012)

    anthony.green (7/9/2012)

    This will do the trick

    DECLARE @d DATETIME = '1984-08-13 00:00:00.000'

    SELECT CONVERT(NVARCHAR(10),@d,120)

    Why are you converting to Nvarchar?? 2 bytes of storage for nothing there, isn't it?

    Force of habit at the moment, we have just changed to global wide so everything is now NVARCHAR to cope with the multi lingual support we need to give to our clients.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply