October 2, 2012 at 7:31 pm
CREATE TABLE product
(
ID INT IDENTITY(1,1)
,VAL VARCHAR(MAX) NOT NULL
);
INSERTproduct
(
VAL
)
VALUES
(
'10/11/2012'
);
INSERTproduct
(
VAL
)
VALUES
(
'cablegrams'
);
INSERTproduct
(
VAL
)
VALUES
(
'Set of data'
);
INSERTproduct
(
VAL
)
VALUES
(
'11/15/2012'
);
INSERTproduct
(
VAL
)
VALUES
(
'12/31/0212'
);
INSERTproduct
(
VAL
)
VALUES
(
'bar'
);
/*** PROBLEM #1 ***/
--Correct the following query:
SELECT*
FROMDATA
WHERECAST(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
thanks
October 2, 2012 at 10:50 pm
harri.reddy (10/2/2012)
/*** PROBLEM #1 ***/--Correct the following query:
SELECT*
FROMDATA
WHERECAST(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
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.
SELECT ID, VAL
FROM dbo.product
WHERE 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, SLine
FROM MaxDate
CROSS APPLY ShortLine;
See if that gets you what you want.
Rob
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply