Link to home
Start Free TrialLog in
Avatar of Overthere
Overthere

asked on

MS SQL and inserting dates into tables using stored procedures

I am using MS SQL Server 2014, the database is running in  2008 compatibility mode. The page I am working with is written in Classical ASP and, must for now, stay that way.
I am having a bad time trying to insert 3 dates into a table using a stored procedure.
 I just can not get the right datatype for the sproc nor the ado parameter type/size right.
Two of the dates, RegBegDate, RegEndDate   are defined as 'date' columns in the table.
They must be formatted to YYYY-mm-dd
The third date, CreateDate, is defined as datetime column in the table. It must be formatted to YYYY-mm-dd
Below is coding from the sproc - ( I can not show all of it-sensitive)
What should my sproc use and what should my ado parameter type be and size?  I left the size parameter out.
Any help appreciated

  @RegBegDate date,
   @RegEndDate date,
   @CreateDate datetime,

Open in new window


And this is the ado parameters:
                rsCmmds.Parameters.Append rsCmmds.CreateParameter("RegBegDate",adDate,adParamInput,,xsdate)
		rsCmmds.Parameters.Append rsCmmds.CreateParameter("RegEndDate",adDate,adParamInput,,xedate)

		rsCmmds.Parameters.Append rsCmmds.CreateParameter("CreateDate",adDBTimeStamp,adParamInput, ,vdate)

Open in new window

Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

how's the values look like for xsdate , xedate and vdate ?
Avatar of Overthere
Overthere

ASKER

thank you for responding. The of thing is if I execute the stored procedure from within MS SQL - it accepts my dates in the format I need.
but not if I programmatically execute my sproc.
I can update those fields by using a recordset but doing it that way seems, to me, to defeat the purpose of the sproc.
for xsdate - 2017-03-23
for xedate - 2017-03-30
for vdate - 3/23/2017 11:20:20 PM

xsdate and xedate must be in the format of YYYY-mm-dd
can you also share the codes how you define your variables and set its value?
ASKER CERTIFIED SOLUTION
Avatar of Big Monty
Big Monty
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you both for responding. Big Monty - that was the ticket! I have messed with dates and sprocs for ages...works great now!
Thank you again :}