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

* Stored ProcMicrosoft SQL ServerASP

Avatar of undefined
Last Comment
Overthere

8/22/2022 - Mon
Ryan Chong

how's the values look like for xsdate , xedate and vdate ?
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
Ryan Chong

can you also share the codes how you define your variables and set its value?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Big Monty

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Overthere

ASKER
Thank you both for responding. Big Monty - that was the ticket! I have messed with dates and sprocs for ages...works great now!
Overthere

ASKER
Thank you again :}