Post reply

Date Problem in Query

  • 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

  • August 5, 2004 at 6:50 am

    #517815

    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

  • August 5, 2004 at 7:11 am

    #517823

    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.

  • August 6, 2004 at 3:46 am

    #517975

    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

  • August 6, 2004 at 10:44 pm

    #518095

    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

  • August 8, 2004 at 11:52 pm

    #518126

    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

  • August 13, 2004 at 4:42 am

    #518832

    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

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

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