date format problem in MSSQL2000

I have 3 SQL Server 2000 with a merge replication between them. On 2 servers, everything goes fine. On the third, my client is unable to get the reports from our VB application when they specify dates.

Regional settings on all servers are US with date format set to M/D/yyyy
Query from our application, taken from Profiler, is:
exec PS_recRapportInventaireDetailled_sel '102', '102', '', '', '', '', '', '', '', '', '2011/03/08 00:00:00', '2011/03/08 23:59:59', '', '', '06', '06', '', '', '', '', '', '', '', '', '2', '', ''

when executed on the 2 first servers, it works fine. on the third server, it give nothing.

On this server, I tried those queries in Query Analyzer:

select * from tb_recReceptionLot
where recdate > '2011-03-08 00:00:00'
-> give nothing

select recBon, recDate from tb_recReceptionLot
order by recDate desc
-> give the following:

recBon recDate
---------- ------------------------------------------------------
1184799854 2011-03-08 10:30:55.310
KSP102 2011-03-08 10:29:53.110
1184799854 2011-03-08 10:28:59.773
P0 2011-03-08 10:28:21.540
1184699854 2011-03-08 10:27:12.943
MAJ102 2011-03-08 10:26:54.047
102 2011-03-08 10:23:36.873
SPJ102 2011-03-08 10:23:17.813
SPJ102 2011-03-08 10:22:02.767
06110163 2011-03-08 10:21:32.073

why does the second select gives correct result and the first query give nothing? I really don't understand where is the problem.

thanks for your time and help  
Dominic34Asked:
Who is Participating?
 
rajeevnandanmishraConnect With a Mentor Commented:
Hi,
The dateformat setting for "u.s. english" installation is default to "mdy".

If in your application initial connection settings, you can put this statement then it will work for that connection.

Or

You can also use proper "SET LANGUAGE" in the session. You can find all the values by running

select * from master..syslanguages
0
 
Nathan RileyFounderCommented:
Does this return it?

select * from tb_recReceptionLot
where recdate > cast('03-08-2011' as datetime)

Open in new window

0
 
Ephraim WangoyaCommented:

select * from tb_recReceptionLot
where recdate > convert(datetime, '2011-03-08 00:00:00', 101)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Dominic34Author Commented:
thanks for the replies.

the first query (exec PS_recRapportInventaireDetailled_sel '102', '102', '', '', '', '', '', '', '', '', '2011/03/08 00:00:00', '2011/03/08 23:59:59', '', '', '06', '06', '', '', '', '', '', '', '', '', '2', '', '') should work as is, with the date formatted this way (YYYY/MM/DD). I don't understand why it don't work when the result from the select query gives the correct date format:
1184799854      2011-03-08 10:30:55.310 => YYYY-MM-DD HH:MM:SS
0
 
rajeevnandanmishraCommented:
Hi,

There should not be any reason because of which the data is not getting returned by first query. It seems that the data is not retrieved when "where" clause is used with recDate column.
Do you have an Index on the table tb_recReceptionLot with column recDate?
If yes, then please rebuilt that index. It is possible that because of corrupt index, the where clause doesn't return correct values.
0
 
Dominic34Author Commented:
the query:
select * from tb_recReceptionLot
where recdate > '2011-03-08 00:00:00'

give result if I set the where to "where recdate > '08-03-2011 00:00:00' (date format MM/DD/YYY). So I guess it's not an index issue.
0
 
Dominic34Author Commented:
This is the actual result of the queries. In the "Untitled 1" query, the exec StoredProc gives nothing. In the "Untitled 8" query windows, you can see the result of the 3 test queries I've made.

Regional setting is actually set to French/Canada, date format YYYY-MM-DD

someone can please explain why the SP don't give any result when it works fine on 2 other replicated servers ?

thanks a lot
result.jpg
0
 
rajeevnandanmishraCommented:
Hi,

Before running the stored procedure, can you put a command
SET DATEFORMAT YMD

And then try running the procedure.
0
 
Dominic34Author Commented:
Hi rajeevnandanmishra

it works with the set dateformat placed before the query.
how can I make it permanent?
0
 
Dominic34Author Commented:
thanks a lot. I added the statement in each StoredProc. it works.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.