SQL Dates Again

[Mute101]

I have written a stored procedure to extract rows from an SQL table and I call the proc from VBA below id a snippet of the code I use.

::Stored Proc::

CREATE PROCEDURE dbo.procMail
@Date datetime,
@Account int
AS
SELECT [LOADS OF FIELDS]
INTO MailTemp 
FROM Orders
WHERE Orders.AccountNo = @Account AND Orders.[Order Status]='i' AND CONVERT(char(10),Orders.DespatchDate,103) = CONVERT(char(10),@Date,103)
EXEC procMailUpdate
GO

::VBA::

Rem #### Set Variables ####
dtmDate = Format(Now(), "dd/mm/yyyy")
intAccount = 18201
Rem #######################

Rem #### Run Stored Proc to Create Table ####
Set cmd = New ADODB.Command
cmd.ActiveConnection = adoCN
cmd.CommandText = "procMail"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("Date", adDBDate, adParamInput, , dtmDate)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("Account", adInteger, adParamInput, , intAccount)
cmd.Parameters.Append prm

cmd.Execute
Set cmd = Nothing
Rem ##########################################

I never get a date match!

What am I doing wrong?

ps sorry about the long post, I couldn't think of a way of explaining without the code.

 

[AWithers]

I think it all depends on how sql expects to see date formatted I know in the version system I use date needs to be formatted 'yyyy/mm/dd'

Andy

 

[Mute101]

In the server properties I have selected 'British English' which I have been lead to believe that dd/mm/yyyy would be appropriate but I will try a different format and see what happens.

Thanks

 

[Mute101]

OK now I am really confused.

SELECT     *
FROM         Orders
WHERE     (CONVERT(char(10), DespatchDate, 103) = '09/11/2001') AND (AccountNo = 16101)

WORKS

but

SELECT     *
FROM         Orders
WHERE     (AccountNo = 16101) AND (DespatchDate = '09/11/2001')

DOESN'T WORK

How come the convert works in the above TSQL but not in the Stored Procedure?

 

[AWithers]

I think that it is the same as I said before and the convert statement puts the date in the same format as the string you are supplying to obtain a match. Does the following work?

One thing that may be causing the problem is that the date field in SQL is actually a date and time field and thus '09/11/2001' is actually midnight and thus you will only find a match where the dispatch date is actually at midnight

SELECT *
FROM Orders
WHERE (AccountNo = 16101) AND (DespatchDate >= '09/11/2001')and (DespatchDate <='10/11/2001')

would give you everything on the '9/11/2001'

PS If its any consolation I hate working with dates

Andy

 

[cmmrfrds]

CONVERT(char(10),Orders.DespatchDate,101) = @Date
NO need for convert function on the variable.

The FORMAT is purely for presentation both input and output. SQL expects the date variable in US English format. The database stores the date as a decimal number where the integer part is the date and the decimal part is the time. The simplest for your needs is to ask to only compare the integer part(date) of the number. You do this by the 101 option in the convert function. The @Date should be in US English format and the SQL will ignore the time part because of the 101 option requested in the database field name used for comparison.

 

[tlbroadbent]

Dates are not that difficult if you learn how they are stored, how they are presented and the diffenrence between storage and presentation. I've written a web page that addresses some of these issues. Please feel free to read it, make comments and/or suggestions.

Handling Dates in SQL Server

http://tlbroadbent.home.attbi.com/sql_server_datetime.htm

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.