SQL & VB DATE COMPARISON

[dicolas]

Hi guys, I'm looking for the best way to compare SQL date with VB date (only date - not time). This way needs to work wathever the SQL dateformat and login language in use.

In a query, I'm using something like :

"... AND CONVERT(DATETIME, CONVERT(INT, CONVERT(FLOAT, MySqlDate))) = '" & Format(int(MyVBDate), sqlDateFormat) & "'"

SQL code is to only select DATE from a datetime field (without time)

VB code is to get DATE without TIME (int function) and to put it in the correct sql date format.

I define the format depending on login language :

SELECT dateformat FROM master.dbo.syslanguages WHERE
(name = (SELECT @@LANGUAGE)).

Case "dmy" => sqlDateformat = "d-m-yy"
Case "mdy" => sqlDateformat = "m-d-yy"
Case "ymd" => sqlDateformat = "yy-m-d"
(It seems that there is no "ydm" SQL dateformat)

Do someone has better suggestion?
Thanks a lot

Regards. Nicolas ;-)

 

[tlbroadbent]

You can simply convert the datetime to a date using the Convert function. Use the VB format function to prepare the comparison date.

Example: ODBC format - SQL style 120

Select * From MyTable
Where Convert(varchar(10), MyDateTime, 120)='2002-02-11'

Example: ISO format - SQL Style 112

Select * From MyTable
Where Convert(varchar(10), MyDateTime, 112)='20020211'

See Cast and Convert in SQL BOL for more information about converting dates and times.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp

There is an excellent article about writing VB code that is compatible with SQL Server or Access at the following link.

http://www.dougdean.com/articles/How2Project6.htm

Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com