Log in  ::  Register  ::  Not logged in

## need help

 Author Message harri.reddy SSC-Enthusiastic Group: General Forum Members Points: 149 Visits: 346 CREATE 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 whitespacethanks robert.gerald.taylor Ten Centuries Group: General Forum Members Points: 1330 Visits: 1399 harri.reddy (10/2/2012)/*** 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 whitespaceIf 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.`SELECT ID, VALFROM dbo.productWHERE ISDATE(VAL) = 1 AND CAST(VAL AS DATETIME) BETWEEN '12/1/2012' AND DATEADD(dd, 1, '12/31/2012');`#2`DECLARE @concatenated VARCHAR(1000) = '';SELECT @concatenated = @concatenated + [VAL] + '|' FROM dbo.product WHERE ISDATE([VAL]) = 0;SELECT @concatenated;`#3`;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;`See if that gets you what you want.Rob