Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

date format problem in MSSQL2000

Posted on 2011-03-08
10
Medium Priority
?
539 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 12

Expert Comment

by:Nathan Riley
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:Ephraim Wangoya
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

598 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