MBR IT/.NET 247 : query and datetime question on ASPFriends.com 'ngfx-sqlclient' list

query and datetime question

Messages

Related Types

This message was discovered on ASPFriends.com 'ngfx-sqlclient' list.

Bryan Andrews

-- Moved from [aspngdata] to [ngfx-sqlclient] by Tony Stark <Click here to reveal e-mail address> --

I am having problems with a query that works in query analyzer... but =
not consistently in the code.

select * from events where edate > '6/23/2002' and Eclubid =3D 512

This query shows events for the end of this month and next, but this =
code:

Dim MyCommand2 As SqlCommand =3D New SqlCommand("select =
ID,edate,Ename,eband,eclub from events where edate > '" & =
System.DateTime.Now.ToShortDateString & "' and eclubID =3D'" & ID & "' =
ORDER BY edate", MyConnection)

Does not show the event for this month - only july. I have other ID's =
than run in this code that do show june events though.=20

There are honestly several little anomalies I am having with data right =
now that is making me wonder if there is some kind of corruption =
happening but wanted to check to see if this query is right along with =
checking the dates within .net.

Thanks for any input.

Reply to this message...

Iain Smallwood

It may not be relevant in this case but as date format can be locale
relevant I reckon best practise is to use the International date format
as below:

"(SQL stuff) where eDate >= {d '" & Strings.Format(DateInVB,
"yyyy-MM-dd") & "'}"

Or

"(SQL stuff) where eDate >= {ts '" & Strings.Format(DateInVB,
"yyyy-MM-dd hh:mm:ss") & "'}"

(the d and ts being date and timestamp respectively)

I DEFINITELY get inconsistent results if I do not do it like that,
however my system date settings are set up. Of course if you use sp's
and parameters there is no conflict. Indeed most of these things are
designed for bizarre American date formats so this may well not be your
problem, plus 23 June is not ambiguous, but its usage does eliminate any
doubt.

HTH
Iain

-----Original Message-----
From: Bryan Andrews [mailto:Click here to reveal e-mail address]
Sent: 24 June 2002 06:05
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] query and datetime question

-- Moved from [aspngdata] to [ngfx-sqlclient] by Tony Stark
<Click here to reveal e-mail address> --

I am having problems with a query that works in query analyzer... but =
not consistently in the code.

select * from events where edate > '6/23/2002' and Eclubid =3D 512

This query shows events for the end of this month and next, but this =
code:

Dim MyCommand2 As SqlCommand =3D New SqlCommand("select =
ID,edate,Ename,eband,eclub from events where edate > '" & =
System.DateTime.Now.ToShortDateString & "' and eclubID =3D'" & ID & "' =
ORDER BY edate", MyConnection)

Does not show the event for this month - only july. I have other ID's =
than run in this code that do show june events though.=20

There are honestly several little anomalies I am having with data right
= now that is making me wonder if there is some kind of corruption =
happening but wanted to check to see if this query is right along with =
checking the dates within .net.

Thanks for any input.

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT

Disclaimer
This message may contain information which is legally privileged and/or
confidential. If you are not the intended recipient, you are hereby
notified that any unauthorised disclosure, copying, distribution or use
of this information is strictly prohibited. Such notification not
withstanding, any comments or opinions expressed are those of the
originator, not of Taylor Made Computer Solutions, unless otherwise
explicitly stated.

Reply to this message...

	System.Data.SqlClient.SqlCommand
	System.DateTime

MBR BootFX
Best-of-breed application framework for .NET projects, developed by Matthew Baxter-Reynolds and MBR IT

	Copyright © Matthew Baxter-Reynolds 2001-2008. '.NET 247 Software Development Services' is a trading style of MBR IT Solutions Ltd.
	Contact Us - Terms of Use - Privacy Policy - www.dotnet247.com