DATETIMEVERY URGENT

  • 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;-)

  • 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

    */

    .

  • 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

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

  • Hello,

    That would include the Minutes as well.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

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

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

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

  • 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
  • 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;-)

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

    .

  • 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)

  • 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

  • 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

  • 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