InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 23:59:42 GMT20http://www.sqlservercentral.com/Forums/Topic1367371-392-1.aspx[quote][b]harri.reddy (10/2/2012)[/b][hr]/*** PROBLEM #1 ***/--Correct the following query:SELECT *FROM DATAWHERE CAST(VAL AS DATETIME) BETWEEN '12/1/2012' AND '12/31/2012' AND ISDATE(VAL) = 1/*** PROBLEM #2 ***/--Write a set of queries that outputs all the non-date values in DATA --as a single pipe-delimited string/*** PROBLEM #3 ***/--Write a single query that returns one row with two columns, the --value of the first column being the latest date and the value of the --second column being the shortest string over 3 characters but--not counting whitespace[/quote]If you want help with your homework, put your code inside IfCode shortcuts to format it properly.#1 assuming you want the end date to be inclusive.[code="sql"]SELECT ID, VALFROM dbo.productWHERE ISDATE(VAL) = 1 AND CAST(VAL AS DATETIME) BETWEEN '12/1/2012' AND DATEADD(dd, 1, '12/31/2012');[/code]#2[code="sql"]DECLARE @concatenated VARCHAR(1000) = '';SELECT @concatenated = @concatenated + [VAL] + '|' FROM dbo.product WHERE ISDATE([VAL]) = 0;SELECT @concatenated;[/code]#3[code="sql"];WITH MaxDate (MaxDt) AS( SELECT MAX([VAL]) AS MaxDt FROM dbo.product WHERE ISDATE([VAL]) = 1),ShortLine (SLine) AS( SELECT TOP 1 [VAL] AS SLine FROM dbo.product WHERE LEN(REPLACE([VAL], ' ', '')) > 3 ORDER BY LEN(REPLACE([VAL], ' ', '')) ASC)SELECT MaxDt, SLineFROM MaxDate CROSS APPLY ShortLine;[/code]See if that gets you what you want.RobTue, 02 Oct 2012 22:50:58 GMTrobert.gerald.taylorhttp://www.sqlservercentral.com/Forums/Topic1367371-392-1.aspxCREATE TABLE product( ID INT IDENTITY(1,1) ,VAL VARCHAR(MAX) NOT NULL);INSERT product( VAL)VALUES( '10/11/2012');INSERT product( VAL)VALUES( 'cablegrams');INSERT product( VAL)VALUES( 'Set of data');INSERT product( VAL)VALUES( '11/15/2012');INSERT product( VAL)VALUES( '12/31/0212');INSERT product( VAL)VALUES( 'bar');/*** PROBLEM #1 ***/--Correct the following query:SELECT *FROM DATAWHERE CAST(VAL AS DATETIME) BETWEEN '12/1/2012' AND '12/31/2012' AND ISDATE(VAL) = 1/*** PROBLEM #2 ***/--Write a set of queries that outputs all the non-date values in DATA --as a single pipe-delimited string/*** PROBLEM #3 ***/--Write a single query that returns one row with two columns, the --value of the first column being the latest date and the value of the --second column being the shortest string over 3 characters but--not counting whitespacethanksTue, 02 Oct 2012 19:31:02 GMTharri.reddy