Create a date from a parameter in SQL stored procedure

You can use the following to manipulate dates:

YEAR(@Date) - Returns Year
MONTH(@DATE) - Returns month
DAY(@DATE) - Returns day
DATEDIFF(Day, @FromDate, @ToDate) - Returns numeric value of difference between 2 dates.
DATEADD(Day, @Num, @Date) - Adds days to date. Can use Month, Year as well.

its  not clear what you wAnt...

PLEASE GIVE US
some examples of the input and expected output

To get to a fixed date/day, surely the 3rd parameter is not the days to increase by but the date/day to hit?

(
      @EFOLDERID nvarchar(50), --FolderID to ensure only certain rows are updated
      @DATETYPE nvarchar (50), --Either Fixed Date or Fixed Day
      @DATE_OR_DAY_DESIRED int,
      @DATEFROM datetime --Either the previous milestone added or if Milestone 1 then todays date
)

If you can confirm that, we can go on from there.

Well, if it is any consolation,

declare @datefrom datetime
set @datefrom = getdate()

declare @dateincrement int
set @dateincrement = 2

select DATEADD(DD,@DATEINCREMENT,@DATEFROM)

Is perfectly legitimate SQL...

To get the next available month (depending on what "available" means) then you can use more date functions

for example, select DATEADD(month,datediff(month,-1,getdate()),0)  should be the first day of next month
and that can be wrapped up in a dateadd(dd,@dateincrement - 1, <the date calculation goes here>)
So, you can imbedd the datefunctions quite deeply.

In a stored procedure you would probably set up a new variable and calculate all that first, and then use the new calculated variable in your update statement.

e.g.

declare @newdate datetime

if (@DATETYPE = 'Fixed Date')
   set @newdate = DATEADD(DD,@DATEINCREMENT,@DATEFROM)
else
   set @newdate = DATEADD(DD,@DATEINCREMENT,@DATEFROM) -- whatever the alternate calculation is...

UPDATE WF_Milestones SET MilestoneDate = @newdate WHERE EFOLDERID = @EFOLDERID

Now, we get onto the more interesting part... The fixed day. That can be a bit more involved, and generally requires a bit of information like your default setting for @@datefirst - which supplies the basis fo day of week calculations (unless you move to an iso week day type calculation which is a bit involved), basically the day of week datepart DATEPART(dw, getdate()) is dependant on the value of @@datefirst and need to take that into account.

But, if you can describe the basis for selecting your date upon which the calculations need to be made, then think we can most definitely help you....

so for fixed date you want the date next month corresponding to that day number?

ie   15 current month is June so you wanr the 15th  of July?

UPDATE WF_Milestones
      SET MilestoneDate = convert(char(6),DATEADD(M,+1,convert(char(6),getdate(),112)+'01'))
                                      +right('0'+convert(varcgar(2),@DATE+OR_DAYDESIRED),2)
WHERE EFOLDERID = @EFOLDERID

if (@DATETYPE = 'Fixed Date')
BEGIN

      UPDATE WF_Milestones
      SET MilestoneDate = convert(char(6),dateadd(m,+1,convert(char(6),getdate(),112)+'01') ,112)+right('0'+convert(varchar(2),@DATE_OR_DAY_DESIRED),2)
      WHERE EFOLDERID = @EFOLDERID
     
END

Thanks for the confirmation.
This should work for you.

if (@DATETYPE = 'Fixed Date')
BEGIN
	declare @firstDayNextMonth datetime
	set @firstDayNextMonth = DATEADD(m, datediff(m,'1900-01-01',@DateFrom)+1, 0)
      UPDATE WF_Milestones
      SET MilestoneDate = DateAdd(d, @DATE_OR_DAY_DESIRED-1,@firstDayNextMonth)
      WHERE EFOLDERID = @EFOLDERID
END
ELSE
BEGIN
	set DATEFIRST 1;
	declare @firstDayNextWeek datetime
	set @firstDayNextWeek = DATEADD(d, 8-datepart(dw,@DateFrom), datediff(d,'1900-01-01',@dateFrom))
      UPDATE WF_Milestones
      SET MilestoneDate = DateAdd(d, @DATE_OR_DAY_DESIRED-1,@firstDayNextWeek)
      WHERE EFOLDERID = @EFOLDERID
END

Select all Open in new window

Well, just add that parameter as a datetime, call it TIME  asn we can extract the time part via: convert(varchar,@time,114)

We can then add that back into any other datetime, or extract that part for comparisons (or use the new TIME data construct in SQL2008).

Really depends on what you want to do with it and how it is going to be used....

OK then try this :

Add in you new parameter at the end - call it @TIME datetime

then down the bottom, just before :

select @newdate

try adding in the line :

set @newdate = convert(char(8),@newdate,112)+' '+convert(varchar,@time,114)

select @newdate


e.g. to show it working....

declare @newdate datetime
declare @time datetime

set @newdate = '20100612 23:23:23.23'
set @time = '19000101 11:12:13.14'

set @newdate = convert(char(8),@newdate)+' '+convert(varchar,@time,114)
select @newdate

OOOPPPSSS....

forgot the 112 style code for the date piece....

convert(char(8),@newdate,112)+' '+convert(varchar,@time,114)

Let's try that example again :)

declare @newdate datetime
set @newdate = '20100612 23:23:23'
declare @time datetime
set @time = '19000101 11:12:13.14'

set @newdate = convert(char(8),@newdate,112)+' '+convert(varchar,@time,114)

select @newdate

@cyberkiwi: *laughing* trying to show us up ? Putting comments in your code, imagine that :)

@Mark

The questioner is obviously really fresh on Sql Server and procs especially date functions, so I oblige with comments.
You may recall I was clarifying requirements way up top, so I am merely following this question through.


I wasn't going to point this out, but the below is really a CPU cycle waster?

   IF len(isnull(@efolderid,'')) < 2 return
   IF charindex(';',@efolderid) > 0 return          -- check for sql injection using ; command delimiter
   IF charindex('--',@efolderid) > 0 return         -- check for sql injection using -- comments
   IF charindex('''',@efolderid) > 0 return         -- check for sql injection using embedded strings ''
   IF charindex('*',@efolderid) > 0 return          -- check for sql injection using /* comments

The parameter is not used in any Dynamic SQL so I can't see the value of these ops... unless I am missing something.  It's entirely possible given you have more experience, so maybe you can shed some light?  From what I can see, having "--" in a Windows folder name is entirely valid.

Sure...

Only happens once at the start, so not a big overhead, well not compared to the possible outcomes :)

Ideally the @efolderid is checked beforehand and is only as big as is minimally needed but it is still possible to inject with using dynamic SQL

The update statement finishes with a "where" clause using the parameter directly ie:

UPDATE WF_Milestones SET MilestoneDate = @newdate WHERE EFOLDERID = @EFOLDERID

So, could easily do something (maybe not such a great example, but, you get the idea) like :

@EFOLDERID = '1;select * from sys.syslogins'

Then the command actually executed becomes :

UPDATE WF_Milestones SET MilestoneDate = @newdate WHERE EFOLDERID = 1;select * from sys.syslogins

So, with any stored proc, unless you can guarantee that there is no possible risk, should do a couple of quick checks, and take whatever other precautions are available.

@mark

I don't know what you're on, but

declare @EFOLDERID varchar(max)
set @EFOLDERID = '1;select * from sys.syslogins'
UPDATE WF_Milestones SET MilestoneDate = @dateResult WHERE EFOLDERID = @EFOLDERID

I'm pretty sure the statement executes the WHERE as comparing the TEXT of EFOLDERID against the string '1;select * from sys.syslogins'.  I still don't get where the SQL injection is happening.

Are you thinking of something else?
In this example, the command being executed is exactly

UPDATE WF_Milestones SET MilestoneDate = @newdate WHERE EFOLDERID = '1;select * from sys.syslogins'

I am on EE drugs I think...