We help IT Professionals succeed at work.

MS SQL and inserting dates into tables using stored procedures

156 Views
Last Modified: 2017-03-24
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

Comment
Watch Question

CERTIFIED EXPERT

Commented:
how's the values look like for xsdate , xedate and vdate ?

Author

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

Commented:
can you also share the codes how you define your variables and set its value?
Web Ninja at large
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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

Author

Commented:
Thank you again :}

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions