Post reply

DATETIMEVERY URGENT

  • September 24, 2008 at 2:52 am

    #193191

    HI

    I WANT TO TAKE OUT THE DATE TIME PART ONLY TILL DATE

    FOR EXAMPLE IF WE HAVE '2008-09-24 14:19:56.293' I WANT '2008-09-24 14:00:00.000'

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • September 24, 2008 at 3:30 am

    #876188

    Try this:

    -- '2008-09-24 14:19:56.293' I WANT '2008-09-24 14:00:00.000'

    DECLARE @d DATETIME

    SELECT @d = '2008-09-24 14:19:56.293'

    SELECT DATEADD(hour, DATEPART(hour, @d), DATEADD(d, 0, DATEDIFF(d, 0, @d)))

    /*

    output:

    -----------------------

    2008-09-24 14:00:00.000

    */

    .

  • September 24, 2008 at 3:39 am

    #876194

    Hello,

    The code below also works.

    Regards,

    John Marsh

    Declare @TestDate DateTime

    Set @TestDate = Convert(Char(13), GetDate(), 121) + ':00:00'

    Select @TestDate

    www.sql.lu
    SQL Server Luxembourg User Group

  • September 24, 2008 at 3:48 am

    #876196

    select convert(varchar(16),getdate(),121)+':00:00'

  • September 24, 2008 at 3:52 am

    #876198

    Hello,

    That would include the Minutes as well.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • September 25, 2008 at 6:26 am

    #876921

    select convert(datetime,convert(varchar(12),getdate(),101),101)

  • September 25, 2008 at 6:26 am

    #876922

    select convert(datetime,convert(varchar(12),getdate(),101),101)

  • September 25, 2008 at 6:26 am

    #876923

    select convert(datetime,convert(varchar(12),getdate(),101),101)

  • September 25, 2008 at 10:23 am

    #877152

    Use Jacob's version, it's faster than the convert to varchar and back. See my blog for the details.

    And please don't shout at us. (post in all caps)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 26, 2008 at 3:29 am

    #877509

    Gila,

    Sorry for posting in caps actually i was in hurry ...so didn't notice that..i will keep in mind

    but question for you ..

    DECLARE @d DATETIME

    SELECT @d = '2008-09-24 14:19:56.293'

    SELECT DATEDIFF(d, 0, @d)

    output :39713

    -----------------------------------------

    what this query returning actually ??

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • September 26, 2008 at 3:34 am

    #877512

    It returns the number of days from '1900-01-01'

    .

  • September 26, 2008 at 8:01 am

    #877709

    While the OP's post indicates a result that includes the hour, I'm pretty sure the words he used preceding the result indicated he wants to eliminate the time part entirely, for which the following should work:

    DECLARE @dt AS DateTime

    SET @dt = '2008-09-24 14:19:56.000'

    SELECT DATEADD(dd,DATEDIFF(dd,0,@DT),0) AS THE_DATE

    The way this works is by determining the difference in days between the original date (@DT) and the zero date, and then adding that number of days to the zero date, which already has a 0 time component. You could use ANY given date instead of just 0, and you can specify it inside quotes in whatever is a valid date format for your environment, as long as you use the same date for both occurrences of 0 in the code, and you either specify 00:00:00.000 for the time part, or better yet, don't specify a time part at all. Does that help?

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • September 26, 2008 at 8:01 am

    #877710

    jacob sebastian (9/24/2008)

    Try this:

    -- '2008-09-24 14:19:56.293' I WANT '2008-09-24 14:00:00.000'

    DECLARE @d DATETIME

    SELECT @d = '2008-09-24 14:19:56.293'

    SELECT DATEADD(hour, DATEPART(hour, @d), DATEADD(d, 0, DATEDIFF(d, 0, @d)))

    /*

    output:

    -----------------------

    2008-09-24 14:00:00.000

    */

    This can be simplied as

    DECLARE @d DATETIME

    SELECT @d = '2008-09-24 14:19:56.293'

    select dateadd(hour,datediff(hour,0,@d),0)

    Simple and effecient 🙂

    Madhivanan

    Failing to plan is Planning to fail

  • September 26, 2008 at 8:04 am

    #877717

    Gayathri.Varadarajan (9/25/2008)

    select convert(datetime,convert(varchar(12),getdate(),101),101)

    This will remove all time portions

    Madhivanan

    Failing to plan is Planning to fail

  • September 26, 2008 at 8:06 am

    #877720

    smunson (9/26/2008)

    While the OP's post indicates a result that includes the hour, I'm pretty sure the words he used preceding the result indicated he wants to eliminate the time part entirely, for which the following should work:

    DECLARE @dt AS DateTime

    SET @dt = '2008-09-24 14:19:56.000'

    SELECT DATEADD(dd,DATEDIFF(dd,0,@DT),0) AS THE_DATE

    The way this works is by determining the difference in days between the original date (@DT) and the zero date, and then adding that number of days to the zero date, which already has a 0 time component. You could use ANY given date instead of just 0, and you can specify it inside quotes in whatever is a valid date format for your environment, as long as you use the same date for both occurrences of 0 in the code, and you either specify 00:00:00.000 for the time part, or better yet, don't specify a time part at all. Does that help?

    Steve

    (aka smunson)

    :):):)

    Well. I havent seen your reply when I posted. Seems we both posted at the same time 🙂

    Madhivanan

    Failing to plan is Planning to fail

Viewing 15 posts - 1 through 14 (of 14 total)

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