British date conversation

I am new to VB and have a problem with returning a recordset from a sql string.

I am using visual studio 2005 and SQL server 2005.

I am attempting to return a recordset with the from a table using two date parameters which are held in another table both tables are on the same server. Both tables store their date values in the British “dd/mm/yyy/ HH:MMS AMPM” format when I attempt to create a ADODB dataset using the following code errors occur. I have attempted various different approaches to resolve this but without success. Can you point me in the right direction?

Regards

Joe

@.@.@.@. CODE @.@.@.@.

‘Sub1

Dim Sdate as string, Edate as string

‘ Both parameters are passed to sub2 from sub1

rstmons.movefirst

Sdate = rstMon.Fields("DateAndTimeofFix").Value

rstmon.movelast

Edate = rstMon.Fields("DateAndTimeofFix").Value

‘Sub2

Dim myCONN As String, MonSql As String

myCONN = ("Driver={SQL Server};Provider=SQLOLEDB;Server=midlaptop2;Database=customerlink;Trusted_Connection=Yes;")

MonSql = "SELECT *" & _

" from tblVsol" & _

"WHERE DateAndTimeofFix Between CONVERT(DATETIME,'" & Sdate & _

"') AND CONVERT(DATETIME,'" & Edate & _

"') and vehicleunit = " & iveh & ";"

use convert with style #103

Select Convert(datetime,'31/1/2007 10:10:00', 103) --Correct

Select Convert(datetime,'31/1/2007 10:10:00') -- Error

MonSql = "SELECT *" & _
" from tblVsol" & _
"WHERE DateAndTimeofFix Between CONVERT(DATETIME,'" & Sdate & _
"',103) AND CONVERT(DATETIME,'" & Edate & _
"',103) and vehicleunit = " & iveh & ";"

|||

Hi

Thanks for your prompt reply.

Thats exactly what I needed.

Many Thanks

Joe

|||

Hi JoeBo,

> Both tables store their date values in the British “dd/mm/yyy/ HH:MMS AMPM” format

Unless you are using a character data type (char / nchar / varchar / nvarchar) to store that data, SQL Server does not store datetime data type using any format. The representation for datatime is a two four bytes, 4 bytes for the date and four bytes for the time. SQL Server interprets datetime constant strings based on the settings of SET LANGUAGE / SET DATEFORMAT, but id you want that SQL Server interpret them correctly no matter of those settings, then use styles 112 or 126 (See function CONVERT in BOL). Also, you should parameterize the "select" statement to re-use cached plans, instead concatenating the values and sending the final statement to SQL Server.

Convert Dynamic SQL to Use SQLCommand Parameters Automatically

http://www.knowdotnet.com/articles/dynamicsqlparameters.html

SqlCommand.Parameters Property

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

Simple Parameterization

http://msdn2.microsoft.com/en-us/library/ms186219.aspx

The ultimate guide to the datetime datatypes

http://www.karaszi.com/SQLServer/info_datetime.asp

AMB