MS SQL Dates ( 7 Views )

1. This question has probably been asked many times before but here goes...
   
   I've got dates stored in a db in full mode ('mm/dd/yyyy hh:mm:ss') and want to pull all the records that are 7 days old, purely based on the date and not the time. Ie. any records created on the 29th July 2003 should be pulled today.
   
   Cheers for any help!
   
   
   (hüseyin, Ghana)


2. Quote:

   Originally Posted by asterix Rudy said it already: "note that the CAST and CONVERT are required to strip off the time portion of GETDATE()"

   Ah, I did miss that. Thanks.
   
   However, they are using style 120, which appears to be "yyyy-mm-dd hh:mi:ss(24h)" according to this Microsoft site I'm at. Was that just oversite or am I missing something more?
   
   Thanks again [img]images/smilies/smile.gif[/img]
   
   Edit: I see now. They used CHAR(10) which will strip the time part off. Gotcha. Thanks so much :)
   
   
   (th, Estonia)


3. Rudy said it already:
   
   "note that the CAST and CONVERT are required to strip off the time portion of GETDATE()"
   
   
   (serhat, French Southern Territories)


4. Hey,
   I know a fair amount of MySQL and now I'm just entering the world of MS SQL. I have to say it's a lot different http://www.sitepointforums.com/images/smilies/smile.gif
   
   I was following this thread, but noticed something that I didn't understand.
   

   Code:

   ---

   set @start_date = dateadd(dd, -7, cast(convert(char(10),getdate(),120) as datetime))

   ---

   
   
   Why do you have to use CONVERT and then CAST? Isn't GETDATE() already in datetime?
   
   Thanks http://www.sitepointforums.com/images/smilies/smile.gif
   
   
   (serhat, Cyprus)


5. thanks shane
   
   i liked your sql, if not your t-sql ;)
   
   whoa, i made two bonehead errors in a single reply, three total in the thread -- must be some kinda personal best
   
   
   (tuba, Equatorial Guinea)


6. Rudy, you got the 2nd and 3rd params, for the DateAdd function, the wrong way round. Also, bigsi wanted records that were exactly 7 days old.
   
   The sql below should do what's needed, without doing the calculation on the datecolumn as Rudy suggested. Also, I've changed the operator for the start date comparison from '>' to '>=' to make sure all possible date values are included.
   
   Test script
   

   Code:

   ---

   declare @date                datetime
declare @start_date        datetime
declare @end_date        datetime

-- Test dates
--set @date        = '2003-07-29 10:00'
--set @date        = '2003-07-30 00:00'
set @date        = '2003-07-30 10:00'
--set @date        = '2003-07-31 10:00'

set @start_date = dateadd(dd, -7, cast(convert(char(10),getdate(),120) as datetime))
set @end_date        = dateadd(dd, 1, @start_date)

-- Test
select        [Date]                = @date,
        [Start Date]        = @start_date,
        [End Date]        = @end_date,
        [Result]        = case
                                when (@date >= @start_date and @date < @end_date)        then 'In Range'
                                when (@date < @start_date)                                then 'Before start date'
                                when (@date > @end_date)                                then 'After end date'
                                else 'Somethings wrong!'
                        end

   ---

   Code:

   ---

   declare @start_date        datetime
declare @end_date        datetime

set @start_date = dateadd(dd, -7, cast(convert(char(10),getdate(),120) as datetime))
set @end_date        = dateadd(dd, 1, @start_date)

select        <date column>
from        <table>
where        (<date column> >= @start_date and <date column> < @end_date)

   ---

   
   
   (merve, Madagascar)


7. aw crap, my bad, you're right, i goofed, wrong database
   
   but my main point still stands -- if you perform a calculation on a date column, any index on that date column might be ignored
   
   therefore what you want to do is

   Code:

   ---

   select stuff
  from yourtable
where datecolumn >
      dateadd(dd
              ,cast(convert(char(10),getdate(),120)
                    as datetime)
              ,-7)

   ---

   perform all the necessary calculations and conversions on the current date, and not on the date column
   
   note that the CAST and CONVERT are required to strip off the time portion of GETDATE()
   
   otherwise the query would only pick up records starting from the same time of day seven days ago as it is at this moment
   
   rudy
   
   
   (mavi, Czech Republic)


8. Rudy, I don't think the from_days and to_days functions exists in MS SQL Server, do they?
   
   This should work for MS SQL Server.
   

   Code:

   ---

   DECLARE @age        INT

SET @age = 7 -- Days

SELECT        <columns>
FROM        <table>
WHERE        DATEDIFF(dd, <date column>, CURRENT_TIMESTAMP) = @age

   ---

   
   
   (ÖZLEM, Sri Lanka)


9. tip: the trick with these types of queries is to make sure you never apply an expression to the date column, but rather, come up with a datetime value as the result of an expression based on the current date
   
   select stuff
   from yourtable
   where datecolumn >
   from_days ( to_days(current_date) - 7 )
   
   note that using current_date avoids the whole issue of truncating the time portion off the datetime value
   
   the result of the from_days() function is a date with no time portion
   
   thus it can be compared to the datetime column efficiently
   
   other approaches to this type of query, which perform a function on the datetime column, might make an index on the datetime column unusable
   
   
   (mansur, Andorra)


10. Thanks guys - sorry for the delay in my reply (been away). I'll check it out and let you know how it goes!
    
    Cheers again..
    
    
    (huseyin, Maldives)

Related Topics ... (or search in 1.720.883 topics !)