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... -
Dont use the Between for datatime datatype .Instead of between use the datediff
u try this qry
select @qty=isnull(sum(line.totalQty),0)
from tbldeliverychallaninfoDetails as line
where datediff(d,main.dcdate,@m_sdate)>=0 and
datediff(d,main.dcdate,@m_edate)<=0
and main.deliveryfrom =@m_locationid and line.productcode=@m_itemcode
-
- I'd say this is not what I'd call a UDF. Transform it into a normal select.
- remember a UDF works at row-level
- "select 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 "
Should do fine. Analyse the query (show execution plan) and provide propre indexe(s).
don't put a function on your column as this makes the predicate non-seargeble.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
hi,
try it out.
main.dcdate, ,@m_sdate , @m_edate must be in
dd/mm/yyyy format.
select isnull(sum(line.totalQty),0)
from tbldeliverychallaninfoDetails as line
where
convert(datetime,main.dcdate,103) between
convert(datetime,@m_sdate,103) and
convert(datetime,@m_edate,103)
and main.deliveryfrom=@m_locationid
and line.productcode=@m_itemcode
bye
nitin
-
Which one is correct way and faster, and what is the internal datetime format for sqlserver “yyyy-mm-dd”
Or “dd-mm-yyyy”
Actually, it's none of these - the internal format is not readable as time and dates to us mere humans.
The internal datetime is stored as two 4-byte integers - one int for the date and the other for the time.
'yyyy-mm'dd', 'dd-mm-yyyy', 'dd/mm/yy' et al are all just display formats - they have absolutely nothing to do with how the data is actually stored.
But, for simplicity and unabigouity, I use to recommend style 112 when working with dates (both reading and writing).
112 is the ISO standard - yyyymmdd - it's the same all around the planet, and the one format that's least likely to be misunderstood.
After all, which date is 01/02/03 really..?
(only I know since I wrote it, and only I know which style to apply in order to deduce which is year, month and day
/Kenneth
-
Hi,
alzdba..sorry i was failed to catch your voice...
What do u mean by:
"- I'd say this is not what I'd call a UDF."
"remember a UDF works at row-level "
I would be happy if you explain it
..Better Than Before... -
- I'd only use a udf if there 's a need to be able to use it as a column _in_ a select clause . all others should go for a stored procedure.
Tends to be the same "discussion" as to datatypedetermination.
maybe these can help to understand what I mean :
- http://support.microsoft.com/support/kb/articles/Q303/3/43.asp?SD=MSDN&FR=0
- http://www.sqlservercentral.com/columnists/dPriyankara/udfascomputedcolumnpart2.asp
- BOL
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The best after me integer
Example:
cast(convert(char(20),your_date_column_name,112) as int)
It can be easily controlled and is faster, much faster that any date or varchar datatype
LMT
-
hi Kenneth Wilhelmsson ,
you are right , but i am facing problem about this date time format,Let clear the scenario, my application is running on a machine where data format is dd-mm-yyyy And my server machine’s date format is mm-dd-yyyy (due to some unavoidable reason this cannot be changed). When I call a function (which takes a datatime data type as parameter) some time query hang. So when I shall invoke the function what will be the date time format? Do I have to convert it? If I do not know in advance the date time format of server machine what will be my date time format for UDF’s datetime field.
So to avoid this problem, I have altered my udf’s datetime parameter to char(10) and passing date as “yyyy-mm-dd” and when I need a comparison I use the following logic: convert(char(10),mydatecol,121)>@myparameter.
But this is a string comparison…. And it takes more time(!) than date comparison.
So I want to pass date time value to UDF what will be the right format that will solve my problem.
Thx in advance
..Better Than Before... -
Create Function sf_Monthlyconsumption(@DD Varchar(2),@MM Varchar(2),@YY Varchar(4))
Returns Table
As
--change sysobjects with your table
Return Select * From Sysobjects Where Cast(Cast(month(CrDate) as varchar(2)) +'/'+Cast(Day(CrDate) as varchar(2))+'/'+Cast(Year(CrDate) as varchar(4)) as DateTime) > Cast(@MM+'/'+@DD+'/'+@YY As DateTime)
Select * from sf_Monthlyconsumption('12','4','2004')
i think this will solve your problem
Rohit -
If time is your main concern, are you sure that a UDF will be faster than an inline conversion?
If you don't know the datesettings on a server beforehand, then I don't believe it would be safe to not provide a date in the ISO format (style 112). You shouldn't have to convert the datetime column at all.
ie
SELECT myDateColumn
FROM myTable
WHERE myDateColumn = CONVERT(CHAR(8),@myparameter,112)
..should work - providing that the dateformat given in the parameter is not ambigous
That is, if the server side has dateformat of mm/dd/yy and you send in 01/02/03 , but sender is meaning the format dd/mm/yy, then there is NO way that the server will know that the intention is the 1st of February instead of the 2nd of January.
The calling side must always know how to phrase the datestring so that the server will interpret it as intended.
I mean, given a date like 01/02/03 - which date is this, really..?
/Kenneth
-
I always pass dates using the three character month. eg: 20 Apr 2004
This way SQL will always convert it to the correct datetime regardless of the regional settings.
This format is easily generated using 106, or 113 if time is needed, as the type parameter in the CONVERT statement.
--------------------
Colt 45 - the original point and click interface
-
My SQL Servers are configured to DD/MM/YYYY format yet still interpret dates as MM/DD/YYYY when passed as strings and converted to datetime.
See my post RE: Date Search for an example of this behaviour.
Therefore it seems no matter what the server settings, SQL Server will always take 01/02/03 to be 2 January 2003.
-
This is not true - and I have been bitten by this a few times.
Never ever use the literal forms - these are dependant on the regional language settings in order to be parsed as a valid date.
ie if the server can't spell the month, it's not a valid date.
Hands up all that have other spellings in their local languages for these months: May, Oct
(hint - on a server with swedish regionale, it's Maj and Okt)
/Kenneth
-
This proves how ambigous these dateformats with delimiters are, and how easy they can be misread.
And you show in your other post how easy it can be to get the wrong dates when relying on the delimited formats.
When I wrote 01/02/03, I intended it to be 1st of February 2003.. or maybe 3rd of February in 2001 (as the Japanese notation would suggest)
The one and only style that holds up against language and datesettings is ISO (style 112) yyyymmdd
...my .02 anyway
=;o)
/Kenneth
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply