I want to get bi-weekly date from one date. Please Help.
As Matt says, it it easier to help you if you provide more information, but I try to guess what you want to accomplish.
If you create a table with number from 0 to X (lets call it num), then you can use a select like the one below:
select DATEADD(w,num,GETDATE()) from NUM where num%2=0
OR if you need to filter a column:
SELECT * FROM myTable WHERE myDateCol IN ( SELECT DATEADD(w,num,GETDATE()) FROM NUM WHERE num%2=0)
I think the big question is "What do you consider the start of your fortnight?" It's one thing to be able to configure your
datefirst setting, but there's no
fortnight first equivalent.
Therefore, you may prefer to do something like:
@knownfortnightstart is a date which you know to be the start of a fortnight, earlier than the range that you're considering. Perhaps take the start of the current fortnight and subtract 28000 from it? Then you can use that as your
@knownfortnightstart value. Once you know it, please use the value as a constant, instead of a variable (which means declaring it, and before you know it, having a function that uses BEGIN and END). The rest of this works because dividing by 14 will always round down to the number of fortnights, and then multiplying back in will get you thinking in days again.
Also, please don't make a scalar function that returns this for you. It would be better for you to use a calculated column in whichever table stores the date you're needing to use, such as:
alter table theTable add fortnight as dateadd(day,datediff(day,'1933-04-03T00:00:00',theDate)/14*14,'1933-04-03T00:00:00')
(April 3rd, 1933 is 28000 days earlier than Monday Nov 30th, 2009)