Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
prompratan
Starting Member
30 Posts |
 Posted - 2006-01-03 : 22:59:11
|
| ***SQL Syntex is select Convert(datetime,t.Time_Date,101) a,Convert(datetime,l.Leave_DateFrom,101) a1,Convert(datetime,l.Leave_DateTo,101) a2from tbl_time t, tbl_leave lwhere Convert(varchar,t.Time_Date,101) between Convert(varchar,l.Leave_DateFrom,101) and Convert(varchar,l.Leave_DateTo,101)--and year(t.Time_Date) = year(l.Leave_DateFrom)and t.Time_EmpID = l.Leave_EmpIDand month(t.Time_Date) = month(getdate())and year(t.Time_Date) = year(getdate())and leave_status in (0,1)and time_empid = 4400082***Result is 2006-01-03 00:00:00.000 2005-01-03 08:00:00.000 2005-01-12 17:00:00.0002006-01-04 00:00:00.000 2005-01-03 08:00:00.000 2005-01-12 17:00:00.0002006-01-05 00:00:00.000 2005-01-03 08:00:00.000 2005-01-12 17:00:00.0002006-01-06 00:00:00.000 2005-01-03 08:00:00.000 2005-01-12 17:00:00.0002006-01-07 00:00:00.000 2005-01-03 08:00:00.000 2005-01-12 17:00:00.0002006-01-08 00:00:00.000 2005-01-03 08:00:00.000 2005-01-12 17:00:00.0002006-01-09 00:00:00.000 2005-01-03 08:00:00.000 2005-01-12 17:00:00.0002006-01-10 00:00:00.000 2005-01-03 08:00:00.000 2005-01-12 17:00:00.0002006-01-11 00:00:00.000 2005-01-03 08:00:00.000 2005-01-12 17:00:00.0002006-01-13 00:00:00.000 2005-01-13 08:00:00.000 2005-01-15 17:00:00.0002006-01-14 00:00:00.000 2005-01-13 08:00:00.000 2005-01-15 17:00:00.0002006-01-03 00:00:00.000 2006-01-03 13:00:00.000 2006-01-03 14:00:00.000*** If I remove commect on condition then I got result is2006-01-03 00:00:00.000 2006-01-03 13:00:00.000 2006-01-03 14:00:00.000*** I want to know about where cause of datetime and convert datetime for condition.Thank you for all answer.prompratan_nakajakawan@hotmail.com |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-03 : 23:18:08
|
It is clearly from your data that all t.Time_Date is of year 2006 and for l.Leave_DateFrom there is only one record which is 2006 the rest are 2005. So if you remove the comments--and year(t.Time_Date) = year(l.Leave_DateFrom) it will only retrieve the record where the year for Time_Date and Leave_DateFrom are the same which is this record2006-01-03 00:00:00.000 2006-01-03 13:00:00.000 2006-01-03 14:00:00.000 -----------------[KH]2006 a new beginning |
 |
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-01-03 : 23:26:18
|
| Hi,I guess fields t.Time_Date,Leave_DateFrom, Leave_DateTo are datetime. then why are you using convert function again to convert in datetime in select statement.and in where clause you are doing string comparision of datevalues by converting them into varchar.the result which you get on uncommenting the line is perfectly alright as it is fetching all records where year is matching. |
|
|
|
prompratan
Starting Member
30 Posts |
Posted - 2006-01-03 : 23:27:51
|
| I am sorry. I think my question not clear. but I know solve this problem.by...where cause is DATEDIFF(y,'1900-01-01 00:00:00.000',t.Time_Date) between DATEDIFF(y,'1900-01-01 00:00:00.000',l.Leave_DateFrom) and DATEDIFF(y,'1900-01-01 00:00:00.000',l.Leave_DateTo)Thank you and sorry khtan. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-03 : 23:47:49
|
quote:
DATEDIFF(y,'1900-01-01 00:00:00.000',t.Time_Date) between DATEDIFF(y,'1900-01-01 00:00:00.000',l.Leave_DateFrom) and DATEDIFF(y,'1900-01-01 00:00:00.000',l.Leave_DateTo)
You can also do thisyear(t.Time_Date) between year(l.Leave_DateFrom) and year(l.Leave_DateTo) ordatepart(year, t.Time_Date) between datepart(year, l.Leave_DateFrom) and datepart(year, l.Leave_DateTo) If your t.Time_Date is such that : l.Leave_DateFrom < t.Time_Date < l.Leave_DateTo This will be faster as it does not need to perform any conversiont.Time_Date between l.Leave_DateFrom and l.Leave_DateTo -----------------[KH]2006 a new beginning |
|
|
|
|
|
|
|
|
Convert condition datetime???