What is the Best Way for Comparing Datetime value in SQLSERVER 2000?

  • I have create a user define function which takes datetime(in my application  I need only date not time) value as a parameter. Form my front end application I am invoking this function as follows:

     

      Qry1 = "select dbo.sf_Monthlyconsumption ('" & Format(sDate, "yyyy-mm-dd") & "', '" & Format(eDate, "yyyy-mm-dd") & "'  )

     

    That is I am passing date parameter as “yyyy-dd-mm” format,

     

    Now at my function  when I am comparing this parameter date with my table’s column date

    I am using this as follows (without having any conversion) :

     

                 select @qty=isnull(sum(line.totalQty),0)                      

                         from tbldeliverychallaninfoDetails as line 

                            where main.dcdate between @m_sdate and

                                           @m_edate and main.deliveryfrom =@m_locationid and

                                                           line.productcode=@m_itemcode  

               RETURN  @qty

               END 

     

    But some time my query hang when call this function, do I have to use convert function as follows.

     

                 select @qty=isnull(sum(line.totalQty),0)                      

                         from tbldeliverychallaninfoDetails as line 

                            where convert(smalldatetime,main.dcdate,121)  between @m_sdate and

                                           @m_edate and main.deliveryfrom =@m_locationid and

                                                           line.productcode=@m_itemcode  

               RETURN  @qty

               END 

    ---

    Or I have to replace datetime parameter with char variable, that means:

    Monthlyconsumption(@sdate char(10),@enddate char(10))

     

    Which one is correct way  and faster, and what is the internal datetime format for sqlserver “yyyy-mm-dd”

    Or “dd-mm-yyyy”

     

    Can you help me on that?

     



    ..Better Than Before...

  • select convert(varchar,sDate,23)  will give you 2004-04-20

    or select convert(varchar,sDate,105) will give you 20-04-2004

    The problem will become the time stamp. If you are querying a datetime field and use a between, with just a date, you will have unreliable results. I usually take the date and append a time to the end so I end up with something like

    set @sDate =CONVERT(VARCHAR, @sDate, 101) + ' 11:59:59.99 PM   where the 101 date format is 04/20/2004. Then use this in my between. Hope this helps.

Viewing 2 posts - 1 through 2 (of 2 total)

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