Is date function - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Is date function

Author

Topic

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-18 : 15:31:08

I have this data in my col like this. How would i pull out just the date with the isfunction? Can somebody help. I researched on line for help and found nothing. The col datatype is nvarchar. I get error converting.

zz01042008CHENZ3
zz01102008honors
zz01312008COLAKK
zz02072008INTSHEWA
zz030308GALLIS
zz041007DISCOG
zz043008mattet1
zz050707EDENG
zz070708MAKEEI
zz081707TCSBALAM
zz090706STEELG
zz122006WALKEL1
zzzzbarren2-28-bulk
zzzzBAUGHM12-28-bulk
zzzzBEBIEG2-28-bulk
zzzzBELLT2-28-bulk
zzzzBENGALLJ12-18-06-Bulk
zzzzBENMOYLD12-18-06-Bulk
zzzzwrongly craeted

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-18 : 15:43:32

This will give you an idea

DECLARE	@Sample TABLE
	(
		data VARCHAR(200)
	)

INSERT	@Sample
SELECT	'zz01042008CHENZ3' UNION ALL
SELECT	'zz01102008honors' UNION ALL
SELECT	'zz01312008COLAKK' UNION ALL
SELECT	'zz02072008INTSHEWA' UNION ALL
SELECT	'zz030308GALLIS' UNION ALL
SELECT	'zz041007DISCOG' UNION ALL
SELECT	'zz043008mattet1' UNION ALL
SELECT	'zz050707EDENG' UNION ALL
SELECT	'zz070708MAKEEI' UNION ALL
SELECT	'zz081707TCSBALAM' UNION ALL
SELECT	'zz090706STEELG' UNION ALL
SELECT	'zz122006WALKEL1' UNION ALL
SELECT	'zzzzbarren2-28-bulk' UNION ALL
SELECT	'zzzzBAUGHM12-28-bulk' UNION ALL
SELECT	'zzzzBEBIEG2-28-bulk' UNION ALL
SELECT	'zzzzBELLT2-28-bulk' UNION ALL
SELECT	'zzzzBENGALLJ12-18-06-Bulk' UNION ALL
SELECT	'zzzzBENMOYLD12-18-06-Bulk' UNION ALL
SELECT	'zzzzwrongly craeted'

SELECT	data AS Original,
	CASE WHEN RIGHT(dta, 1) = '-' THEN LEFT(dta, LEN(dta) - 1) ELSE dta END AS Extracted,
	ISDATE(CASE WHEN RIGHT(dta, 1) = '-' THEN LEFT(dta, LEN(dta) - 1) ELSE dta END) AS ISDATE
FROM	(
		SELECT	data,
			SUBSTRING(dta, 1, PATINDEX('%[^0-9-]%', dta) - 1) AS dta
		FROM	(
				SELECT	data,
					SUBSTRING(data, PATINDEX('%[0-9]%', data), LEN(data)) AS dta
				FROM	@Sample
			) AS d
	) AS d

E 12°55'05.63"
N 56°04'39.26"

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-18 : 15:45:07

WHAT ABOUT DATA IN RED, It does not look like complete date, as possibly year is missing ?

zz01042008CHENZ3
zz01102008honors
zz01312008COLAKK
zz02072008INTSHEWA
zz030308GALLIS
zz041007DISCOG
zz043008mattet1
zz050707EDENG
zz070708MAKEEI
zz081707TCSBALAM
zz090706STEELG
zz122006WALKEL1
zzzzbarren2-28-bulk
zzzzBAUGHM12-28-bulk
zzzzBEBIEG2-28-bulk
zzzzBELLT2-28-bulk
zzzzBENGALLJ12-18-06-Bulk
zzzzBENMOYLD12-18-06-Bulk
zzzzwrongly

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-18 : 15:47:16

Data in red is also data in the column

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-18 : 16:05:42

Hi Peso,

I have a better idea about your approach.
However, i get this error
Msg 536, Level 16, State 5, Line 3222
Invalid length parameter passed to the SUBSTRING function.
where would i make any changes?

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-18 : 16:15:35

Hi Peso,

Please disregard my request. I reduced the num of rows and it worked. Thanks a lot.

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-18 : 16:29:20

Hi Peso,

Sorry to say that reducing the number of rows quite does not help.
Is there any changes i need to make for this error?

Msg 536, Level 16, State 5, Line 18
Invalid length parameter passed to the SUBSTRING function.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-18 : 17:08:26

[code]SELECT data AS Original,
CASE WHEN RIGHT(dta, 1) = '-' THEN LEFT(dta, LEN(dta) - 1) ELSE dta END AS Extracted,
ISDATE(CASE WHEN RIGHT(dta, 1) = '-' THEN LEFT(dta, LEN(dta) - 1) ELSE dta END) AS ISDATE
FROM (
SELECT data,
SUBSTRING(dta, 1, PATINDEX('%[^0-9-]%', dta + ':') - 1) AS dta
FROM (
SELECT data,
SUBSTRING(data, PATINDEX('%[0-9]%', data), LEN(data)) AS dta
FROM @Sample
) AS d
) AS d[/code]

E 12°55'05.63"
N 56°04'39.26"

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-19 : 11:42:58

Thank you Peso. It worked. I appreciate your help. Maachie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources