Change time of a datetime attribute











up vote
0
down vote

favorite












I have this query:



select id from table where date >= @idate


I want to set the time of the date attribute in the where = 00:00



How can I do it?










share|improve this question




























    up vote
    0
    down vote

    favorite












    I have this query:



    select id from table where date >= @idate


    I want to set the time of the date attribute in the where = 00:00



    How can I do it?










    share|improve this question


























      up vote
      0
      down vote

      favorite









      up vote
      0
      down vote

      favorite











      I have this query:



      select id from table where date >= @idate


      I want to set the time of the date attribute in the where = 00:00



      How can I do it?










      share|improve this question















      I have this query:



      select id from table where date >= @idate


      I want to set the time of the date attribute in the where = 00:00



      How can I do it?







      sql sql-server-2005






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Feb 2 '10 at 20:47









      Peter Mortensen

      13.3k1983111




      13.3k1983111










      asked Feb 1 '10 at 13:13









      Luca Romagnoli

      4,5472777146




      4,5472777146
























          3 Answers
          3






          active

          oldest

          votes

















          up vote
          0
          down vote



          accepted










          e.g.



          CAST(CONVERT(VARCHAR(10), date, 120) AS DATETIME)


          Note that this will not make good use of any index on the date field, and so you may see poor performance especially with higher data volumes.



          Alternatives would be to:

          - re-evaluate what you're trying to achieve and how

          - store the date and time elements of the field in 2 separate, indexable fields.






          share|improve this answer























          • i don't want change @idate but date
            – Luca Romagnoli
            Feb 1 '10 at 13:18










          • just change @idate to date, as updated. Note that this prevents good index usage
            – AdaTheDev
            Feb 1 '10 at 13:19


















          up vote
          1
          down vote













          Best to use range scans. Why do you need to change the value stored in the column? If you want to find everything that happened on 2009-09-15 (regardless of time), you can say:



          WHERE [date] >= '20090915'
          AND [date] < '20090916';


          Now you will still be able to use an index on the [date] column, if it exists, which it arguably should if you are running queries like this often. Converting on the left hand side leads to non-SARGable queries which will almost unilaterally suck performance-wise.



          A useful link to check out is Tibor's article on date/time data types, including querying tips:



          http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes



          I also wrote a pretty lengthy article on avoiding bad practices when querying date ranges:




          • Bad habits to kick : mis-handling date / range queries






          share|improve this answer






























            up vote
            0
            down vote













            You should never change the column information unless absolutely necessary - this causes a full table scan and ignores indexes. Just make sure @idate is correct.






            share|improve this answer





















              Your Answer






              StackExchange.ifUsing("editor", function () {
              StackExchange.using("externalEditor", function () {
              StackExchange.using("snippets", function () {
              StackExchange.snippets.init();
              });
              });
              }, "code-snippets");

              StackExchange.ready(function() {
              var channelOptions = {
              tags: "".split(" "),
              id: "1"
              };
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function() {
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled) {
              StackExchange.using("snippets", function() {
              createEditor();
              });
              }
              else {
              createEditor();
              }
              });

              function createEditor() {
              StackExchange.prepareEditor({
              heartbeatType: 'answer',
              convertImagesToLinks: true,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: 10,
              bindNavPrevention: true,
              postfix: "",
              imageUploader: {
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              },
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              });


              }
              });














               

              draft saved


              draft discarded


















              StackExchange.ready(
              function () {
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f2176839%2fchange-time-of-a-datetime-attribute%23new-answer', 'question_page');
              }
              );

              Post as a guest















              Required, but never shown

























              3 Answers
              3






              active

              oldest

              votes








              3 Answers
              3






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes








              up vote
              0
              down vote



              accepted










              e.g.



              CAST(CONVERT(VARCHAR(10), date, 120) AS DATETIME)


              Note that this will not make good use of any index on the date field, and so you may see poor performance especially with higher data volumes.



              Alternatives would be to:

              - re-evaluate what you're trying to achieve and how

              - store the date and time elements of the field in 2 separate, indexable fields.






              share|improve this answer























              • i don't want change @idate but date
                – Luca Romagnoli
                Feb 1 '10 at 13:18










              • just change @idate to date, as updated. Note that this prevents good index usage
                – AdaTheDev
                Feb 1 '10 at 13:19















              up vote
              0
              down vote



              accepted










              e.g.



              CAST(CONVERT(VARCHAR(10), date, 120) AS DATETIME)


              Note that this will not make good use of any index on the date field, and so you may see poor performance especially with higher data volumes.



              Alternatives would be to:

              - re-evaluate what you're trying to achieve and how

              - store the date and time elements of the field in 2 separate, indexable fields.






              share|improve this answer























              • i don't want change @idate but date
                – Luca Romagnoli
                Feb 1 '10 at 13:18










              • just change @idate to date, as updated. Note that this prevents good index usage
                – AdaTheDev
                Feb 1 '10 at 13:19













              up vote
              0
              down vote



              accepted







              up vote
              0
              down vote



              accepted






              e.g.



              CAST(CONVERT(VARCHAR(10), date, 120) AS DATETIME)


              Note that this will not make good use of any index on the date field, and so you may see poor performance especially with higher data volumes.



              Alternatives would be to:

              - re-evaluate what you're trying to achieve and how

              - store the date and time elements of the field in 2 separate, indexable fields.






              share|improve this answer














              e.g.



              CAST(CONVERT(VARCHAR(10), date, 120) AS DATETIME)


              Note that this will not make good use of any index on the date field, and so you may see poor performance especially with higher data volumes.



              Alternatives would be to:

              - re-evaluate what you're trying to achieve and how

              - store the date and time elements of the field in 2 separate, indexable fields.







              share|improve this answer














              share|improve this answer



              share|improve this answer








              edited Feb 1 '10 at 13:24

























              answered Feb 1 '10 at 13:16









              AdaTheDev

              104k22163173




              104k22163173












              • i don't want change @idate but date
                – Luca Romagnoli
                Feb 1 '10 at 13:18










              • just change @idate to date, as updated. Note that this prevents good index usage
                – AdaTheDev
                Feb 1 '10 at 13:19


















              • i don't want change @idate but date
                – Luca Romagnoli
                Feb 1 '10 at 13:18










              • just change @idate to date, as updated. Note that this prevents good index usage
                – AdaTheDev
                Feb 1 '10 at 13:19
















              i don't want change @idate but date
              – Luca Romagnoli
              Feb 1 '10 at 13:18




              i don't want change @idate but date
              – Luca Romagnoli
              Feb 1 '10 at 13:18












              just change @idate to date, as updated. Note that this prevents good index usage
              – AdaTheDev
              Feb 1 '10 at 13:19




              just change @idate to date, as updated. Note that this prevents good index usage
              – AdaTheDev
              Feb 1 '10 at 13:19












              up vote
              1
              down vote













              Best to use range scans. Why do you need to change the value stored in the column? If you want to find everything that happened on 2009-09-15 (regardless of time), you can say:



              WHERE [date] >= '20090915'
              AND [date] < '20090916';


              Now you will still be able to use an index on the [date] column, if it exists, which it arguably should if you are running queries like this often. Converting on the left hand side leads to non-SARGable queries which will almost unilaterally suck performance-wise.



              A useful link to check out is Tibor's article on date/time data types, including querying tips:



              http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes



              I also wrote a pretty lengthy article on avoiding bad practices when querying date ranges:




              • Bad habits to kick : mis-handling date / range queries






              share|improve this answer



























                up vote
                1
                down vote













                Best to use range scans. Why do you need to change the value stored in the column? If you want to find everything that happened on 2009-09-15 (regardless of time), you can say:



                WHERE [date] >= '20090915'
                AND [date] < '20090916';


                Now you will still be able to use an index on the [date] column, if it exists, which it arguably should if you are running queries like this often. Converting on the left hand side leads to non-SARGable queries which will almost unilaterally suck performance-wise.



                A useful link to check out is Tibor's article on date/time data types, including querying tips:



                http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes



                I also wrote a pretty lengthy article on avoiding bad practices when querying date ranges:




                • Bad habits to kick : mis-handling date / range queries






                share|improve this answer

























                  up vote
                  1
                  down vote










                  up vote
                  1
                  down vote









                  Best to use range scans. Why do you need to change the value stored in the column? If you want to find everything that happened on 2009-09-15 (regardless of time), you can say:



                  WHERE [date] >= '20090915'
                  AND [date] < '20090916';


                  Now you will still be able to use an index on the [date] column, if it exists, which it arguably should if you are running queries like this often. Converting on the left hand side leads to non-SARGable queries which will almost unilaterally suck performance-wise.



                  A useful link to check out is Tibor's article on date/time data types, including querying tips:



                  http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes



                  I also wrote a pretty lengthy article on avoiding bad practices when querying date ranges:




                  • Bad habits to kick : mis-handling date / range queries






                  share|improve this answer














                  Best to use range scans. Why do you need to change the value stored in the column? If you want to find everything that happened on 2009-09-15 (regardless of time), you can say:



                  WHERE [date] >= '20090915'
                  AND [date] < '20090916';


                  Now you will still be able to use an index on the [date] column, if it exists, which it arguably should if you are running queries like this often. Converting on the left hand side leads to non-SARGable queries which will almost unilaterally suck performance-wise.



                  A useful link to check out is Tibor's article on date/time data types, including querying tips:



                  http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes



                  I also wrote a pretty lengthy article on avoiding bad practices when querying date ranges:




                  • Bad habits to kick : mis-handling date / range queries







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Nov 10 at 22:50

























                  answered Feb 1 '10 at 13:32









                  Aaron Bertrand

                  206k27358401




                  206k27358401






















                      up vote
                      0
                      down vote













                      You should never change the column information unless absolutely necessary - this causes a full table scan and ignores indexes. Just make sure @idate is correct.






                      share|improve this answer

























                        up vote
                        0
                        down vote













                        You should never change the column information unless absolutely necessary - this causes a full table scan and ignores indexes. Just make sure @idate is correct.






                        share|improve this answer























                          up vote
                          0
                          down vote










                          up vote
                          0
                          down vote









                          You should never change the column information unless absolutely necessary - this causes a full table scan and ignores indexes. Just make sure @idate is correct.






                          share|improve this answer












                          You should never change the column information unless absolutely necessary - this causes a full table scan and ignores indexes. Just make sure @idate is correct.







                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Feb 1 '10 at 13:20









                          cjk

                          38.7k56898




                          38.7k56898






























                               

                              draft saved


                              draft discarded



















































                               


                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function () {
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f2176839%2fchange-time-of-a-datetime-attribute%23new-answer', 'question_page');
                              }
                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown







                              Popular posts from this blog

                              Bressuire

                              Vorschmack

                              Quarantine