Date Format & stored procedures

[sergeiY]

Hi

I have this annoying problem with ASP/SQL flipping month and day in dates...

I've got a stored proc that expects 2 date params and I have an ASP page that executes it here it is:

Set Cmd = Server.CreateObject("ADODB.Command")
With Cmd
	.ActiveConnection = strConn
	.CommandText = "sp_Synchronise" ' name of stored procedure
	.CommandType = adCmdStoredProc
	.parameters.refresh
	.parameters.item("@startDate") = startDate
	.parameters.item("@endDate") = endDate	
	Set rs = Server.CreateObject("ADODB.Recordset")
	Set rs = .Execute
	
	'clean up
	Set rs = Nothing
	Set Cmd = Nothing	
End With

I know I need to execute SQL command:

SET DATEFORMAT dmy

before call to my stored proc but how do I do it ??

I know I can execute it on Connection object and then execute my SQL directly on connection it works no problem but in this case I need to execute stored proc...

the only option I know will work with my curent ASP code will be to add SET DATEFORMAT dmy to my stored proc but it's not fesable at the moment as I have no control over stored procs...

Is there any way to enforse specific date format through ASP/ADO ???

Thank you in advance

Sergei

 

[clarkin]

if startDate and endDate are string variables, just use a format that is never ambiguous.. like say (dd mon yyyy) so for example "01 Aug 2003" or "22 Jan 1776".
VBscript is particularly annoying (in this respect) in that it has a FormatDateTime function, but no option to produce a non ambiguous date


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[Veep]

Here's a tidbit on date formatting:

http://www.aspfaq.com/show.asp?id=2313

Also...

Don't use the command object:

http://www.aspfaq.com/show.asp?id=2191

 

[sergeiY]

clarkin

you did it again Thank you. I should've looked at your answer in my other thread I see answer for this question

Sergei