Date format SQL

[Bennov]

Hello,

I have two pc's, both with Windows XP SP2 and Office 2003 SP2 (dutch) and the same input locales. I made a program in Access (connected to a MS SQL Server) where dates are stored. Now I want to built a report with a daterange. Because of the dateformat in SQL I made a function to convert the date because they enter it in Dutch format. The function is:

Function DatumSQL(datDatum As Date) As String
DatumSQL = "#" & Format(datDatum, "mm") & "-" & Format(datDatum, "dd") & "-" & Format(datDatum, "yyyy") & "#"
End Function

This works great on computer 1. But not on computer 2. On the second computer the date is converted again. When I don't use the function it works fine on computer 2 but not anymore on computer 1.
I searched for differences and found that on computer 1 the files in the map C:\Program Files\Common files\System\ado are from 3-8-2004 and on computer 2 they are from 2-3-2006.

How can this happen? And what is the solution?

I hope someone can help me.

Thanks in advance.

Benno

 

[Guus2005]

Always tricky dates are. Store them as long integers. 73050 = 31-12-2099. Never fails.

 

[Bennov]

Re

Hello,

I don't think that's an option.... I use a lot of datefields in various programs and didn't have this problem over years. Until now....

I installed on both computers MS SQL Server Enterprise Manager and used the query analyzer for the following result of the sql statement:

"SELECT [tblOffertes].*, Klant00.Bedrijf, Klant00.Aard
FROM [tblOffertes] LEFT JOIN KLANT00 ON [tblOffertes].KLANTNR = KLANT00.KLANTNR
WHERE ((([tblOffertes].OPDRACHT) Between '1/15/2007' And '12/31/2007')
AND ((KLANT00.AARD) like '%') AND (([tblOffertes].Onderdeel) Like '%')
AND (([tblOffertes].AKTIE) Like '%'))"

Ofcourse it works on 1 computer but the second one says:
'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'

This is with the same query analyzer on the same sql database!

 

[Guus2005]

Hoi Bennov,
Perhaps you have to convert the dates between quotes to datetypes before you compare them to OPDRACHT.

SELECT [tblOffertes].*, Klant00.Bedrijf, Klant00.Aard 
FROM [tblOffertes] LEFT JOIN KLANT00 ON [tblOffertes].KLANTNR = KLANT00.KLANTNR 
WHERE ((([tblOffertes].OPDRACHT) Between [B]'1/15/2007'[/B] And [B]'12/31/2007'[/B]) 
AND ((KLANT00.AARD) like '%') AND (([tblOffertes].Onderdeel) Like '%') 
AND (([tblOffertes].AKTIE) Like '%'))

A little rusty i am with SQL Server syntax. You could use CAST or CONVERT for this task i believe.

Enjoy!

 

[Bennov]

Sql 2005

Thanks for the replies but I didn't get it to work. I now upgraded from SQL Server 2000 to 2005 en it works fine on all computers!