Solved

date format problem in MSSQL2000

Posted on 2011-03-08
10
523 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
ID: 35071437
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
ID: 35071494

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

Author Comment

by:Dominic34
ID: 35071891
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
ID: 35072055
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
ID: 35072101
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:Dominic34
ID: 35072297
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
ID: 35072554
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
ID: 35072663
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
ID: 35072764
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
ID: 35072798
thanks a lot. I added the statement in each StoredProc. it works.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

867 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

20 Experts available now in Live!

Get 1:1 Help Now