Dates Problem

[agina1]

hi....

Can anyone help me on this ASAP??

I have 2 fields to compare ...A and B fields..Both of this fields are datetime data type..

The problem is B field is using getdate() as the value.. Where by A value is coming from text file..

For example like this..

A - 2003-09-22 00:00:00
B - 2003-09-22 17:05:00

When I try to compare this two dates for sure it is not the same...

Is there anyway I can solve this..I don't want to add column from DatePart function where I can chop the date to month,day,year columns..

Please help me on this..Thanks

[skhanal]

Use CONVERT function to convert the dates to same format and compare.

[MAK]

This always workd for any comparision. Just a matter of preference what format you like to use.


declare @date datetime
set @date ='9/24/2003'
select convert(varchar(10),@date,112)
select convert(varchar(10),getdate(),112)

[YuckFou]

USE datediff to compare dates...

select datediff( day , '2.1.2003 00:00:01' , '2.1.2003 00:00:00')

if datediff(day..) returns 0 the dates are same.

[agina1]

thanks yuckfou....

it works when i tried you matter...

thanksssssssss muaahhhhh

[dlhatheway]

Using datediff will scan the index and test all values. If you structure your comparison to compare to a constant date only (actually a time of midnight), you are less likely to get a complete index scan.

select <list>
from <yourtable>
where a = convert(datetime,convert(varchar(12),getdate(),101 ))

will probably be faster than

select <list>
from <yourtable>
where datediff(day,a,getdate()) = 0

This example works in the US - there may be local date representations to work out elsewhere.

[YuckFou]

but if you compare two fields in one table sqlserver is doing every time table scan.

[dlhatheway]

No, it will use an index, if any of the indexes are selective enough.

I presume you mean a query like:

select <list>
from <table>
where <col1> = <somevalue>
and <col2> = <somevalue>

if col1 or col2 is part of a 'good' index, SQL Server will do an 'index seek'. If the index isn't terribly selective, SQL Server might still do an 'index scan' which may save time - and it will may use more than one index (if both col1 and col2 are indexed).

[YuckFou]

No I mean select like

select <list>
from <table>
where convert(varchar(10), <col1> ,112)= convert(varchar(10), <col2> ,112)

use table scan and also

select <list>
from <table>
where datediff ( day , <col1> , <col2>) = 0

[r3sann]

hello..

i have a proble i made a statement that the user can i select the items but i have a problem for example the in the datebase their are five item only four will display in the window the first one cannot be seen i dont why..heres my code:

<? $get_desc="select parts_desc,parts_no,parts_sqn_no from parts_mst where cust_code='$cust'";
$exec_desc=odbc_exec($connect,$get_desc);
$parts_sqn_no=odbc_result($exec_desc,"parts_sqn_no ");
?>
<select name="desc[<? echo $c?>]">
<option selected value="---">select name </option>
<?
while(odbc_fetch_row($exec_desc)){
printf("<option value=%s>%s%s</option>",odbc_result($exec_desc,"parts_sqn_no"),od bc_result($exec_desc,"parts_no"),odbc_result($exec _desc,"parts_desc"));
}
?>
</select>


thanks,
r3sann