Solved

date format problem in MSSQL2000

Posted on 2011-03-08
10
526 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

805 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