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!
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
And this is the ado parameters:
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,
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)
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
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.
.Parameters.Append .CreateParameter( "DOB", adDBTimeStamp, adParamInput, "8", dob )
in fact, I believe you can (and should) use adDBTimeStamp for both data types.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial