MS SQL and inserting dates into tables using stored procedures

Overthere
Overthere used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

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
Ryan ChongSoftware Team Lead

Commented:
can you also share the codes how you define your variables and set its value?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Web Ninja at large
Commented:
I use the following for DateTime stamps and it works perfectly:

.Parameters.Append  .CreateParameter( "DOB", adDBTimeStamp, adParamInput, "8", dob )

in fact, I believe you can (and should) use adDBTimeStamp for both data types.

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial