I want to get bi-weekly date from one date. Please Help.
asked
get biweekly startdate and enddate from one date
You're going to need to provide some more detail if you want an answer that is actually correct... From your question as-is, I could just add two weeks to it and say 'there, job done'. But I would presume that's not what you're actually asking for.
I know this is an old question but another question might be... how many bi-weekly dates do you want to generate from one date?
SELECT GETDATE(), DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 14 * 14, 0)
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:
select dateadd(day,datediff(day,@knownfortnightstart,getdate())/14*14,@knownfortnightstart)
...where @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)
