Avatar of Overthere
 asked on

MS SQL 2008 and stored prodcures and dates

I am using  MS SQL Sever 2014 - my database is running in 2008 compatibility mode.
In my asp page (classical asp) I am programmatically executing a stored procedure.
The table I am inserting into has a datetime column.
I have looked and looked but I can not find what the parameter datatype  is called- I think it is adDate.
The date to be inserted is in the format YYYY-mm-dd i.e. 2017-03-20
Can someone tell me what it is and if my date programmatically will insert. It worked okay when I executed the sproc from within
MS SQL Server Mamnagement but now it will be within my coding on my asp page. Thank you.
Here is the line of coding:

  rsCmmds.Parameters.Append rsCmmds.CreateParameter("RegBegDate",adDate,adParamInput,12,vdate)

Open in new window

ASPSQLMicrosoft SQL Server 2008

Avatar of undefined
Last Comment

8/22/2022 - Mon
Nitin Sontakke

Sorry, lost touch with classic ASP. However, please feel free to take it if it helps, otherwise simply ignore.

To my knowledge, sql server will happily take string as a date "so long as" it successfully converts it to legitimate date, for example, 2017-03-20 or even 20170320. You can try it yourself in SSMS.

In addition, I think you should format your date as YYYY-MM-dd and NOT YYYY-mm-dd, because small m is for the minute and not for month. Which could be causing an issue. Please check.
Nitin Sontakke

Will you also not need @ before parameter name?

Thank you for responding. I can identify with your statement about classic ASP but its like riding a bicycle! The @ is used when creating the stored proc in MS - which is what I did, The small mm was a good catch. Its correct in my coding where I convert the date. I just wasn't sure if
the parameter datatype was adDateTime or just adDate and I have searched and searched....
It will be a day or two before I can retest - having issues with IIS 7 not letting my pages display - can't find them! Sigh
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Nitin Sontakke

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
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

I was just reading a article about it at W3c Schools! Thank you!