April 18, 2004 at 10:52 pm
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...
April 20, 2004 at 1:30 pm
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