datetime => how to have complete date with 00:00:00.000

  • Hi,

    i work with sql server 2005 and i 'm a beginner

    I have a table with a column datetime.

    in this table i have some date as this :

    2008-10-28 11:04:04.207

    2009-03-30 13:26:10.433

    2009-01-16 14:06:20.033

    but i'd like when i do a query select * from myTable to have the date in format

    2008-10-28 00:00:00.000

    2009-03-30 00:00:00.000

    2009-01-16 00:00:00.000

    How i can do this !?

    Thanks in advance

    Christophe

  • A number of ways of doing this:

    SELECTCONVERT(datetime,CONVERT(char(8),MyDateTimeColumn,112))

    FROM MyTable

    or

    SELECTCONVERT(datetime,DATEDIFF(DD,0,MyDateTimeColumn))

    FROM MyTable

    Hope that helps.

    Mike

  • One way I often do this:

    CREATE TABLE #MyTable

    (

    MyDate DATETIME

    )

    INSERT INTO #MyTable

    SELECT '2008-10-28 11:04:04.207' UNION ALL

    SELECT '2009-03-30 13:26:10.433' UNION ALL

    SELECT '2009-01-16 14:06:20.033'

    SELECT MyDate,

    DATEADD(dd, DATEDIFF(dd, 0, MyDate), 0) MyDate_DateOnly

    FROM #MyTable

    DROP TABLE #MyTable

    Hope this helps.

    Cheers,

    Simon 🙂

  • Hi both,

    thanks for your sample, it's ok now !

    thanks for your time

    christophe

  • Here is a link to some common date routines:

    http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

    CEWII

  • Be a bit careful about using character conversions of dates, folks. They're s-l-o-w. Might not make any difference with just a couple of thousand rows but if you work with millions of rows, it can really add up in a hurry.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff, what do you suggest as a best-practice for returning a date with 00:00:00.000?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Either of the following is what I consider to be a "best practice". They're both very fast but the first one edges out the second when it comes to millions of rows if you need to apply it to something that large.

    SELECT CAST(DATEDIFF(dd,0,GETDATE()) AS DATETIME),

    DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

    Replace the GETDATE() with whatever date you may have including dates in a column from a table. Just don't forget to add the FROM clause for the table. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • You may find this useful too:

    http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/[/url]

  • Jeff Moden (4/7/2011)


    Either of the following is what I consider to be a "best practice". They're both very fast but the first one edges out the second when it comes to millions of rows if you need to apply it to something that large.

    SELECT CAST(DATEDIFF(dd,0,GETDATE()) AS DATETIME),

    DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

    Replace the GETDATE() with whatever date you may have including dates in a column from a table. Just don't forget to add the FROM clause for the table. 😀

    Ah... I just had a horrible thought based on how the original question was posed. If you're trying to use such a thing as criteria for a JOIN or a WHERE clause, DO NOT use either of the two methods above on the COLUMN of a TABLE. Instead, do this...

    SELECT something

    FROM SomeTable

    WHERE SomeDateColumn >= SomeWholeDate

    AND SomeDateColumn < DATEADD(dd,1,SomeWholeDate)

    The code above finds all the rows in SomeTable that have a date (with or without time) that falls on SomeWholeDate and it does so while still allowing an index on SomeDateColumn to be used. Using just about any formula on SomeDateColumn itself will prevent the use of an index.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • But wouldn't that defeat the purpose of getting a date like "2011-04-08 00:00:00.000" in the first place?

    SELECT DATEADD(dd,0,GETDATE()) results in "2011-04-08 09:17:05.080"

    Consider I have a developer who writes this:

    WHERE ([NextDateTime] between CONVERT(DATETIME,(CONVERT(VARCHAR(10),getdate(),121) + ' 00:00:00.000'))

    and CONVERT(DATETIME,(CONVERT(VARCHAR(10),getdate(),121) + ' 23:59:59.999'))) and [Disabled] = 0

    What they are trying to do is grab the data that fits these 2 date and times...surely there's a better way to do so (they use this type of CONVERTing and so forth to make JOINS and use it in WHERE clauses all over the place).

    What is the most efficient way to accomplish this?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • MyDoggieJessie (4/8/2011)


    But wouldn't that defeat the purpose of getting a date like "2011-04-08 00:00:00.000" in the first place?

    SELECT DATEADD(dd,0,GETDATE()) results in "2011-04-08 09:17:05.080"[/code]

    I never suggested that code or anything like it. Go back and look again.

    {edit} Ah... sorry. Didn't know what you were driving at. You're correct. The trouble is I thought you were working with WHOLE dates already.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • MyDoggieJessie (4/8/2011)


    Consider I have a developer who writes this:

    WHERE ([NextDateTime] between CONVERT(DATETIME,(CONVERT(VARCHAR(10),getdate(),121) + ' 00:00:00.000'))

    and CONVERT(DATETIME,(CONVERT(VARCHAR(10),getdate(),121) + ' 23:59:59.999'))) and [Disabled] = 0

    What they are trying to do is grab the data that fits these 2 date and times...surely there's a better way to do so (they use this type of CONVERTing and so forth to make JOINS and use it in WHERE clauses all over the place).

    What is the most efficient way to accomplish this?

    First, the method that is written is actually incorrect because 23:59:59.999 will round up to the NEXT day at midnight.

    Also, as you may have guessed, the conversion of the dates to VARCHAR is pretty slow to being with. Add concatenation into the mix and you get a fair bit slower.

    One of the two fastest ways to rewrite the WHERE clause you posted is as follows...

    WHERE NextDateTime >= CAST(DATEDIFF(dd,0 ,GETDATE()) AS DATETIME) --Today starting at midnight

    AND NextDateTime < CAST(DATEDIFF(dd,-1,GETDATE()) AS DATETIME) --Tomorrow starting at midnight

    AND [Disabled] = 0

    NEVER use BETWEEN on dates with times.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • NEVER use BETWEEN on dates with times.

    Why?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Using between to compare datetimes is a didatic concept and this practice isn't optimazed.

    May this article help you.

    http://databases.aspfaq.com/general/should-i-use-between-in-my-database-queries.html

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

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