Solved

date format problem in MSSQL2000

Posted on 2011-03-08
10
521 Views
Last Modified: 2012-05-11
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  
0
Comment
Question by:Dominic34
10 Comments
 
LVL 11

Expert Comment

by:N R
Comment Utility
Does this return it?

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

Open in new window

0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

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

Author Comment

by:Dominic34
Comment Utility
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
 
LVL 9

Expert Comment

by:rajeevnandanmishra
Comment Utility
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
 

Author Comment

by:Dominic34
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Dominic34
Comment Utility
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
 
LVL 9

Expert Comment

by:rajeevnandanmishra
Comment Utility
Hi,

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

And then try running the procedure.
0
 

Author Comment

by:Dominic34
Comment Utility
Hi rajeevnandanmishra

it works with the set dateformat placed before the query.
how can I make it permanent?
0
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:Dominic34
Comment Utility
thanks a lot. I added the statement in each StoredProc. it works.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now