InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 05:24:43 GMT20http://www.sqlservercentral.com/Forums/Topic1008387-149-1.aspxGreat!!!!!!!!!!!!!... Working fine... Thanks chris....Thanks a lot...Thu, 21 Oct 2010 08:33:10 GMTBeginnerBughttp://www.sqlservercentral.com/Forums/Topic1008387-149-1.aspxWhy do you think the query should return only one row (or none) regardless of how many rows may or may not match the filter? I'm not sure if this statementCONVERT(VARCHAR(11), LTRIM(RTRIM('01-JUL-2010')), 106)actually does anything at all. Try this:SELECT CONVERT(VARCHAR(11), LTRIM(RTRIM('41-JUL-2010')), 106).The string dates cannot be sensibly compared as they stand. They must be converted to proper dates for SQL Server to be able to compare them properly. Both the columns and the variables. Here's some stuff for you to play with:[code="sql"]create table #trp_table (from_date varchar(15),to_date varchar(15))insert into #trp_table(from_date,to_date)SELECT '01-JUL-2010', '22-JUL-2010' UNION ALLSELECT '15-JUL-2010', '20-JUL-2010' UNION ALLSELECT '11-FEB-2010', '27-JUL-2010' DECLARE @To_Date varchar(15), @from_date varchar(15), @To_DateDT DATETIME, @from_dateDT DATETIME SET @from_date = '01-JUN-2010'SET @To_Date = '30-JUN-2010'SET @from_dateDT = CONVERT(DATETIME, @From_Date, 106)SET @To_DateDT = CONVERT(DATETIME, @To_Date, 106)-- check variables are good:SELECT FromDate = @from_dateDT, ToDate = @To_DateDT-- check date ranges:SELECT From_date, To_date, Errors = CASE WHEN (From_date >= @from_dateDT AND From_date <= @To_DateDT) AND (To_date >= @from_dateDT AND To_date <= @To_DateDT) THEN 1 ELSE 0 ENDFROM ( -- convert string to datetime in this inner query or derived table SELECT From_date = CONVERT(DATETIME, From_Date, 106), To_date = CONVERT(DATETIME, To_Date, 106) FROM #trp_table) d[/code]Thu, 21 Oct 2010 08:14:04 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1008387-149-1.aspx[code="sql"]create table trp_table (from_date varchar(15),to_date varchar(15))insert into trp_table(from_date,to_date)select CONVERT(VARCHAR(11), LTRIM(RTRIM('01-JUL-2010')), 106), CONVERT(VARCHAR(11), LTRIM(RTRIM('22-JUL-2010'))) UNION ALLSELECT CONVERT(VARCHAR(11), LTRIM(RTRIM('15-JUL-2010')), 106), CONVERT(VARCHAR(11), LTRIM(RTRIM('20-JUL-2010')))UNION ALLSELECT CONVERT(VARCHAR(11), LTRIM(RTRIM('11-FEB-2010')), 106), CONVERT(VARCHAR(11), LTRIM(RTRIM('27-JUL-2010')))DECLARE @lb_error_flag BITSELECT @lb_error_flag = CASE WHEN (from_date >= CONVERT(VARCHAR(11), LTRIM(RTRIM( '01-JUN-2010')), 106) and from_date <= CONVERT(VARCHAR(11), LTRIM(RTRIM( '30-JUl-2010')), 106) )AND (to_date >= CONVERT(VARCHAR(11), LTRIM(RTRIM( '01-JUN-2010')), 106) and to_date <= CONVERT(VARCHAR(11), LTRIM(RTRIM( '30-JUN-2010')), 106)) THEN 1 ELSE 0 ENDFROM trp_tableprint @lb_error_flag[/code]Still it returns 1... where it should be 0.. 3rd row differ from the from_date and to_date.....and select select CONVERT(VARCHAR(11), LTRIM(RTRIM('01-JUL-2010')), 106) works in my environment....select isdate(CONVERT(VARCHAR(11), LTRIM(RTRIM('01-JUL-2010')), 106)) returns 1Thu, 21 Oct 2010 07:43:55 GMTBeginnerBughttp://www.sqlservercentral.com/Forums/Topic1008387-149-1.aspx[quote][b]MonsterRocks (10/21/2010)[/b][hr]then how to convert a varchar type variable into datetime type?..[/quote]Look up CONVERT in BOL, and choose the 'Style' which matches your text date. 106 looks promising even though the date part delimiter is different:SELECT CONVERT(DATETIME, '10-jun-2010', 106)Check that this works against your data, then create a sample data script something like this:CREATE TABLE #table1 (ID INT, meat VARCHAR(25)) INSERT INTO #table1 (ID, meat) SELECT 1, 'beef pork' UNION ALL SELECT 2, 'pork chicken' UNION ALL SELECT 3, 'pork chicken beef' and post it here for reference.Your query will look something like this:SELECT Fromdate, Todate, Errors = CASE WHEN (Fromdate >= @pd_from_date and Fromdate <= @pd_to_date)AND (Todate >= @pd_from_date and Todate <= @pd_to_date) THEN 1 ELSE 0 ENDFROM MyTableThu, 21 Oct 2010 07:10:14 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1008387-149-1.aspxthen how to convert a varchar type variable into datetime type?..Thu, 21 Oct 2010 06:57:09 GMTBeginnerBughttp://www.sqlservercentral.com/Forums/Topic1008387-149-1.aspxhai chris..both are varchar types.... specification is like tht where i cant change that as datetime data type...Thu, 21 Oct 2010 06:45:30 GMTBeginnerBughttp://www.sqlservercentral.com/Forums/Topic1008387-149-1.aspxWhat datatype are the 'date' columns in your table?Thu, 21 Oct 2010 06:38:54 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1008387-149-1.aspxi have a table as follows... -------------------------------From date | To _date-------------------------------10-jun-2010 | 30-jun-201020-jun-2010 | 30-aug-201003-aug-2009 | 30-aug-2010--------------------------------i have @pd_from_date='1-jun-2010' and @pd_to_date='31-aug-2010'i have a flag @lb_error_flag bit... it should be 1 if date from table not between @pd_from_date and @pd_to_dateif all date in the tbl between @pd_from_date and @pd_to_date then it should be 0...[code="sql"]DECLARE @lb_error_flag BITSET @lb_error_flag=0SELECT @lb_error_flag=1 FROM Trp_table WHERE CONVERT(VARCHAR(11), LTRIM(RTRIM(From_date)), 106) < @pd_from_date AND CONVERT(VARCHAR(11), LTRIM(RTRIM(To _date)), 106) > @pd_to_date[/code]_dateam i right?... or any other better approach?... Thu, 21 Oct 2010 06:27:35 GMTBeginnerBug