We help IT Professionals succeed at work.

MS SQL 2008 and stored prodcures and dates

199 Views
Last Modified: 2017-12-24
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

Comment
Watch Question

Nitin SontakkeDeveloper
CERTIFIED EXPERT

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

Commented:
Will you also not need @ before parameter name?

Author

Commented:
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
Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

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