We help IT Professionals succeed at work.

Create a date from a parameter in SQL stored procedure

Hi Everyone,

Im really having a hard time understanding dates and how to calculate them.

I have a form where there are two dropdowns ...

Dropdown 1 - displays two options - Fixed Date / Fixed Day
Dropdown 2 - dependent on what is selected will display 1-31 if Fixed Date or Mon-Sun if fixed day.

I then have a button to calculate the date which will run a stored procedure, which at the moment looks like this ...

(
      @EFOLDERID nvarchar(50), --FolderID to ensure only certain rows are updated
      @DATETYPE nvarchar (50), --Either Fixed Date or Fixed Day
      @DATEINCREMENT int, --Value to increase the date type by
      @DATEFROM datetime --Either the previous milestone added or if Milestone 1 then todays date
)
AS
      SET NOCOUNT ON;

if (@DATETYPE = 'Fixed Date')
BEGIN

      UPDATE WF_Milestones SET MilestoneDate = DATEADD(DD,@DATEINCREMENT,@DATEFROM) WHERE EFOLDERID = @EFOLDERID

END


I have only started it for the first date type at the moment so I dont bite off more than I can chew, I have read quite a lot about calculating dates but nothing really specific to what I want to achieve. Hopefully you can help, but if anyone has any good resources for creating/calculating  dates then I would be very greatful again.

Thanks in advance.

Comment
Watch Question

Commented:
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.

Author

Commented:
OK thanks for that, I think I may be getting somewhere now!

So I have managed to extract the pieces of the date, so flipping this around I need to create a date by "glueing" them together?

How can I bring these bits together to form a date and then update my column with that date?

Top Expert 2011

Commented:
its  not clear what you wAnt...

PLEASE GIVE US
some examples of the input and expected output

Author

Commented:
The input would be the day (1-31) which will then be added to the next available month. The output will be a date, which I need to update my date column in my database table.

Thanks.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
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.

Author

Commented:
Yes sorry, your right there. The dateadd function is misleading to you.

Thanks.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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....

Top Expert 2011

Commented:
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

Author

Commented:
Yes thats right, I pass through a number which will represent the date for the next month.

I have run this (sorry I think I corrected a few typos in your code) ...

if (@DATETYPE = 'Fixed Date')
BEGIN

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

And I get this error back ...

Msg 241, Level 16, State 1, Procedure usp_MajorMilestoneDateManager, Line 20
Conversion failed when converting date and/or time from character string.

But in answer to you Mark, there will be three date creation scenarios.

1 - What I am investigating at the moment, setting the date to be a particular date in a month. For example, 1st July, 10th July etc.

2 - Where the user selects a specific DAY of the week, for example every Monday or Wednesday etc.

3 - A relative date, for example 5 days before or after a specific date, and so on. I am hoping the dateadd functionality will come into its own there.

Im having fun with learning about dates today, i've even sacrificed the first game of the world cup to do this :)

Thanks so far guys, this has been really helpful.

Top Expert 2011

Commented:
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
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
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

Open in new window

Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Here you go...

Checked for next instance if not yet happened e.g. if today is the 12th June and we select 21 and 'Fixed Date' then should return 21st June, but, if we select say 9 then should return 9th July. Same thing for Day of Week.  Should be obvious where to comment out that part if you always want the next period.

So, think we have all three covered off. If you dont specify a value for @DATEFROM then it assumes getdate() ie "today"

Also the days of week converts / uses ISO days ie Mon = 1, Tue = 2, Wed = 3 .....  Sun = 7

Also put in some standard error checking and that type of thing - nothing too much, but as an example only.

Anyway once you create the procedure, have a play with all the combos, then comment out the select @newdate and un-comment the update.

Always test on a copy or backup or in development first.

Hope it address all the combos, and only too happy to decipher anything for you.

CREATE PROCEDURE usp_update_milestone
(
   @EFOLDERID nvarchar(50), --FolderID to ensure only certain rows are updated
   @DATETYPE nvarchar (50), --Either Fixed Date or Fixed Day
   @DATEINCREMENT int, --Value to increase the date type by
   @DATEFROM datetime --Either the previous milestone added or if Milestone 1 then todays date
)
AS
BEGIN

   SET NOCOUNT ON;

   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

   IF isnull(@datefrom,'19000101') < '20000101' set @datefrom = convert(char(8),getdate(),112)
   IF isnull(@dateincrement,0) < 1 set @dateincrement = 1
      
   DECLARE @newdate datetime

   if (isnull(@DATETYPE,'Fixed Date') = 'Fixed Date')
      begin
         if @dateincrement > day(@datefrom)
            set @newdate = DATEADD(DD,@DATEINCREMENT - 1,DATEADD(month,datediff(month,0,@datefrom),0))     -- next occurance this month (not yet happened)
         else
            set @newdate = DATEADD(DD,@DATEINCREMENT - 1,DATEADD(month,datediff(month,-1,@datefrom),0))    -- next occurance next month
      end
   else
      if (isnull(@DATETYPE,'') = 'Days Offset' )
         set @newdate = DATEADD(DD,@DATEINCREMENT,@DATEFROM) 
      else -- must be day of week
         begin -- ISO day numbers : Mon=1,Tue=2,Wed=3.....Sun=7
            if @dateincrement > ((((DATEPART(dw, @datefrom) - 1) + (@@DATEFIRST - 1)) % 7) + 1)
               set @newdate = dateadd(dd,@dateincrement-((((DATEPART(dw, @datefrom) - 1) + (@@DATEFIRST - 1)) % 7) + 1), @datefrom)  -- this weeks occurance of day (not yet happened)
            else
               set @newdate = dateadd(dd,7+@dateincrement-((((DATEPART(dw, @datefrom) - 1) + (@@DATEFIRST - 1)) % 7) + 1), @datefrom) -- next weeks occurance of day
         end

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

END
GO

Open in new window

Author

Commented:
Thanks so much for this guys, it has been a massive help and I am very nearly there!

If you dont mind me asking one more thing then I will be done.

I need to pass through a time. This is done via a datepicker that just shows the time. The time will not be mandatory so will be passed through as NULL sometime so it will just represent the date. So this will be a parameter that then needs to be added to the date.

I have noticed that for fixed date, it does not do anything with the time, but for the others it passes through the current time.

Nearly there now, thanks so much for your help with this so far.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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....

Author

Commented:
Thanks, so the time will be added to the @newdate parameter when all calculations are complete. This is just a specific time of the day they want logged for the created day.

So it has no specific functionality as such at this point, it will later on in the process, but for the purposes of creating this date it is just the time related to the date that is created.

I think im just a little cautious about adding the time to @newdate at the end of the procedure!
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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


Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Hope this helps your understanding.
I have annotated it so that you can understand it.  It is a different approach from mark's, and uses no CONVERT.  Just date functions throughout.
(
      @EFOLDERID nvarchar(50), --FolderID to ensure only certain rows are updated
      @DATETYPE nvarchar (50), --Either "Fixed Date" or "Fixed Day" or anything else means "days offset"
      @DATE_OR_DAY_DESIRED int,
      @DATEFROM datetime, --Either the previous milestone added or if Milestone 1 then todays date
      @TIME datetime -- time in the resultant date
)
AS
SET NOCOUNT ON;

declare @dateResult datetime
if (@DATETYPE = 'Fixed Date')
BEGIN
	declare @firstDayTargetMonth datetime
	-- (1) datediff(m,'1900-01-01',@DateFrom) -=> month offset from "date 0"
	-- (2) case when DAY(@dateFrom) >= @DATE_OR_DAY_DESIRED then 1 else 0 end -=> if we are past the day of month,
	--     we go to next month (i.e. +1) else stay on current month (i.e. +0)
	-- (3) DATEADD(m, <inner>, 0) -=> goes to the month required; the day of month will end up as 1st
	--     This also drops any time information from the datetime
	set @firstDayTargetMonth = DATEADD(m, datediff(m,'1900-01-01',@DateFrom)+
		case when DAY(@dateFrom) >= @DATE_OR_DAY_DESIRED then 1 else 0 end, 0)
	-- the formula takes off 1 day, then adds back the desired day of month
	SET @dateResult = DateAdd(d, @DATE_OR_DAY_DESIRED-1,@firstDayTargetMonth)
END
ELSE if (@DATETYPE = 'Fixed Day')
BEGIN
	-- (4) makes Sql Server work with 1=Monday, 2=Tuesday etc
	set DATEFIRST 1;
	declare @firstDayTargetWeek datetime
	-- (5) case when DATEPART(dw,@dateFrom) >= @DATE_OR_DAY_DESIRED -=> if we are past the day of week,
	--     we add 7 more days to the formula, e.g. difference betweeh 8 and 1
	-- (6) 1-datepart(dw,@DateFrom) -=> whatever week we are in, this is the adjustment required to move the date to Monday
	--     8-datepart(dw,@DateFrom) -=> same as above, except it is Monday the next week
	-- (7) datediff(d,'1900-01-01',@dateFrom) -=> This is one of many ways to remove the time from a datetime
	--     ensuring that @dateFrom has no time, or in other words the time is 00:00:00
	-- (8) dateadd(d, <adjustment to move to Monday>, <date without time>) -=> The result fom this is
	--     Monday in the desired week, with no time information
	set @firstDayTargetWeek = DATEADD(d,
		case when DATEPART(dw,@dateFrom) >= @DATE_OR_DAY_DESIRED then 8 else 1 end
		-datepart(dw,@DateFrom), datediff(d,'1900-01-01',@dateFrom))
	-- the formula takes off 1 day, then adds back the desired day of week
	SET @dateResult = DateAdd(d, @DATE_OR_DAY_DESIRED-1,@firstDayTargetWeek)
END
ELSE -- days from @datefrom
	SET @dateResult = DateAdd(d, @DATE_OR_DAY_DESIRED, datediff(d,'1900-01-01',@dateFrom))

-- This adds an optional time parameter. YES, adding time like this is OK
SET @dateResult = @dateResult + ISNULL(@time,0)

-- finally update the table
UPDATE WF_Milestones
SET MilestoneDate = @dateResult
WHERE EFOLDERID = @EFOLDERID

Open in new window

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
@cyberkiwi: *laughing* trying to show us up ? Putting comments in your code, imagine that :)
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
@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.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
@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'
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
I am on EE drugs I think...

Author

Commented:
Thankyou both so much for your help! They both work and have helped me understand date calculations. I wish I had something a bit easier to try and start off with but theres no end like the deep end when it comes to learning stuff like this :)

Thanks again for your hard work I really appreciate it.