Another Date Problem!

[JamesHardiman]

I have a bunch of records in a table. First column called ValueDate, created thus:

INSERT INTO tblStockValues SELECT getdate() as ValueDate, blah blah...

If I run this from Query Analyser:
SELECT CONVERT(varchar(30), ValueDate, 113) AS blob, ProductId FROM tblStockValues

I get this:

blob ProductId
20 Aug 2003 12:01:19:530 9002

If I run this in VBA behind an access ADP form:
x = Nz(DMax("ValueDate", "tblStockValues&quot, 0)

it produces this: 20/08/2003 12:01:20

Which means that this:
cn.Execute "DELETE FROM tblStockValues WHERE ValueDate='20/08/2003 12:01:20'"

(or any sensible variation thereof) doesn't get the *^%!records, due to the 0.47 second difference in times.

This, in Query analyser:
select max(ValueDate) from tblStockValues

gives this:
2003-08-20 12:01:19.530

whereas this in VB:
Set rs = OpenRS("select max(ValueDate) as x from tblStockValues", adOpenStatic, adLockReadOnly)
datTodaysValuation = rs!x

gives this:
20/08/2003 12:01:20

I've tried using FLOAT and truncating, but it gives completely silly answers!

All I'm trying to do is give the user the chance to delete the set of records with the specified DateTime on them.

Any/all help VERY gratefully received, before I put boot through computer screen.

Note: this isn't a UK Date format problem, it's a rounding/significance problem (I'm 99% sure!)

Regards,

James Hardiman
Sunsail, UK

 

[Jamfool]

Mmm maybe add to the where clause? to delete all those between 000 part of a sec, and 999

DECLARE @date1 varchar (35)
DECLARE @moomoo varchar (435)
SELECT @date1 = '2003-08-20 16:08:48'

set @moomoo = 'select * from matters where created >= '+''''+@date1+'.000'+''''
+' and created <= '+''''+@date1+'.999'+''''

print @moomoo
exec (@moomoo)

 

[TheBugSlayer]

How precise do you expect the user to enter the time> Is hh:mm alone enough or do you want hh:mm:ss?

It is very unlikely that the user will enter the datetime value up to the second, therefore you need to convert both the datetime value and the field value to a format such as dd/mm/yyyy hh:nn:00.

So try using the DATEPART function to concatenate the datetime parts in the format you want and then using CONVERT to format the value and comparing them....or try something like '...WHERE cast(convert(varchar, @MyDate1, 101) as datetime) = cast(convert(varchar, @MyDate2, 101) as datetime)'

Hope that helps.

 

[JamesHardiman]

Mm. As usual, I'm getting an education here!

Thanks; with your help I got there:

Alter Procedure &quot;aaSP&quot;

as

DECLARE @date1 varchar (35)
DECLARE @moomoo varchar (435)
SELECT @date1 = '2003-08-20 16:25:40'

set @moomoo = 'select * from tblStockValues where ValueDate >= convert(datetime,' + '''' + @date1 + '.000' + ''''
+', 102) and ValueDate <= convert(datetime, ' + '''' + @date1 + '.999' + '''' + ', 102)'

print @moomoo
exec (@moomoo)

Sometimes SQL can be soooo intuitive!

James Hardiman
Sunsail.