CODEWITHSUNDEEP
sqlsql-serversql-server-2008
asfsdf
asfsdf

Reputation: 175

how can I get last 7 days of data being my date field a number?

I need to get last 7 days data excluding Sunday being my date field a number. How can I do it? Field structure 20140425. For example is I run the statement today it should give me date range between 20140424 - 20140417 excluding 20140420.

Upvotes: 0

Views: 76

Answers (2)

Jeremy Hutchinson
Jeremy Hutchinson

Reputation: 2045

The hitch is of course converting the number based date to a real date. This seems to work:

select convert(datetime, convert(char(10), 20140425))

To expand, your query would look like this:

select *
  from [Table]
 where convert(datetime, convert(char(10), [columnname])) between convert(varchar, getdate() - 8, 101) and convert(varchar, getdate() - 1, 101)
   and datepart(DW, convert(datetime, convert(char(10), [columnname]))) <> 1

The convert(varchar, getdate - 1, 101) will return you 12:00am yesterday morning. My first pass didn't include that and would've only given a 6 day range.

Upvotes: 1

M.Ali
M.Ali

Reputation: 69534

SELECT * 
FROM Table_Name 
WHERE CAST(DateField AS DATE) >= DATEADD(DAY, -7, GETDATE())
AND   CAST(DateField AS DATE) <=   GETDATE()
AND   DATEPART(DW,CAST(DateField AS DATE)) <> 1

Upvotes: 0

Related Questions