Date Problem in Query

Question

Post reply

Date Problem in Query

Manish_

Ten Centuries

Points: 1070
More actions
August 4, 2004 at 10:16 pm

#161299

Dear Experts
How can i solve the following problem , when i execute this query on MSSQLSERVER 2000
select ec_type,code,msgid from emc_messageinfo where datediff dd,msgdate,'02/08/2004 4:15:04 PM')=0
It return nothing ...........
and when i execute the same query by writing "AUG" in place of 08 it execute.
select ec_type,code,msgid from emc_messageinfo where datediff(dd,msgdate,'02/aug/2004 4:15:04 PM')=0
Please guide me as your earliest.

- Manish

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Tal Mcmahon SSC Eights! Points: 850 More actions · Answer 1

Hi, first you are missing a paran after data add

Both Of These Work:

Select * from Report where datediff(dd,Recorddate,'02/08/2004 4:15:04 PM')<100

Select * from Report where datediff(dd,Recorddate,'02/aug/2004 4:15:04 PM')<100

HTH

tal McMahon

Kindest Regards,

Tal Mcmahon

Terry Murphy-161740 Old Hand Points: 361 More actions · Answer 2

The problem most likely is to do with the locallisation of the particular machine you are running. It may be getting confused between the 02/08 as the second of august and 02/08 as the 8th February. Putting in the aug makes it explicit and the conversion from the string will override the local settings.

Japie Botma SSCrazy Points: 2990 More actions · Answer 3

You can solve the problem Terry explained by converting the date to an explicit format:

select ec_type,code,msgid from emc_messageinfo where datediff( dd,convert(datetime, msgdate, 113),convert (datetime,'02 AUG 2004 16:15:04',113))=0

You can change the 113 to fit your format. Check the convert command in BOL.

5ilverFox
Consulting DBA / Developer
South Africa

Manish_ Ten Centuries Points: 1070 More actions · Answer 4

Dear repliers.

Thanks for your reply.

but again there is the problem ! i will write this query in the source code of the VB.net form, which will execute this query in the Sqlserver,

and this query is coming from multiple clients on the network having different machines with different localized setting !

Is there any other way of solving this problem, so that without depending upon the localised setting of the machine i can execute my query which comes from different machines to the Sql Server !

Regards

Manish Kaushik

- Manish

~nano SSC Eights! Points: 804 More actions · Answer 5

You can write a function upfront in VB .NET. There you can get the date and split it into day, month, year (See Documentation VB .NET for Date manipulation) and rebuild it in the order you need as a string.

When you pass this to SQL you then use CONVERT (BOL has good description for date formats) to match the string to your datetime collumn.

Greetings from Germany

~nano

Dølle Døck Mr or Mrs. 500 Points: 577 More actions · Answer 6

You could try something like this:

select

ec_type,

code,msgid

from emc_messageinfo

where datediff(dd,msgdate,convert(DateTime,'02/08/2004', 103)=0

Regards,

Anders Dæmroen
epsilon.no