0

I have a table which has more than 90 million rows, it has data for more than 3 years. I want to keep all the records added on Monday (or any one specific day in a week) and delete the remaining records for the data added a year before.

Can any one help me with this?

The difficulty I am facing is to sort out the data that was added on Monday (or any specific day of the week)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Swethu
  • 37
  • 10
  • Use `DATEPART`. Also, are you sure you want to _delete_ the old data? Would there be anything wrong with archiving it somewhere, and they querying it with `DATEPART` if you only want to see Monday records? – Tim Biegeleisen Oct 09 '18 at 02:05
  • please do a backup before running your query – Gonzalo.- Oct 09 '18 at 02:05
  • Thank all :) @TimBiegeleisen data is archived need the current table to hold only monday records – Swethu Oct 09 '18 at 02:32

2 Answers2

0

As Tim said, you can try

datepart(weekday, your_date) = 2

to check whether the date is Monday (note that 1 is Sunday). Or even you can try

datename(weekday, your_date) = 'Monday'

to set the weekday name explicit

Ivien
  • 427
  • 2
  • 5
  • I like the `DATENAME` suggestion much less than `DATEPART`, because the former might return something _other_ than `Monday`, if the server language is not English. – Tim Biegeleisen Oct 09 '18 at 02:55
0

You can use datepart

DATEPART(dw, '2007-04-21 ') = 2 //  day of the week.

DATENAME(weekday, '2007-04-21 ') = 'Monday'

More can be found https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017

Naruto
  • 653
  • 8
  • 19
  • 2
    Tip: `( @@DateFirst + DatePart( weekday, SampleDate ) - 1 ) % 7 + 1` will always return an integer from `1` to `7` with `1` corresponding to Sunday regardless of the setting of `DateFirst` or `Language`. – HABO Oct 09 '18 at 03:11