how to extract date from a string

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

SQL Server 2005 Forums

Transact-SQL (2005)

how to extract date from a string

Author

Topic

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-20 : 08:56:00

hello all,
i have a immediate work to deliver please help me

X1 Y1
1 abcdefghi
2 02022011abcfer
3 01011999xyzsqw
4 10192011sdfsse

suppose their is a table contain two column X1 and Y1
X1 -->int type
Y1 -->varchar type

if you see the table carefully the 2nd and 3rd column contains a date
2feb2011(ddmmyyyy)XXXXX format and in 3rd column 1jan1999(ddmmyyyy)XXXXX
So my question is i want to fetch the rows which contains valid date
in single a query
Eg-: when the query is executed it should fetch 2 and 3 row bcoz they are valid and it should not fetch 1 and 4 row because they are not valid

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-12-20 : 09:05:46

something like:


select convert(datetime,substring('01012011dgchdv',5,4) + '-' + substring('01012011dgchdv',3,2) + '-' + substring('01012011dgchdv',1,2)) 
where isdate(substring('01012011dgchdv',5,4) + '-' + substring('01012011dgchdv',3,2) + '-' + substring('01012011dgchdv',1,2)) = 1

To be fair though, if you are getting paid for this, you really need to learn yourself. I nearly didn't bother because it usually annoys me when people ask for an answer urgently.. Means they can't do the jobs they are employed to do or they've left their school assignment until the last minute..

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-20 : 09:11:37

I have tried very much..but i didn't find the way

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-20 : 09:22:46

it does not return the correct result

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-12-20 : 10:10:23

It returns correctly for the examples you gave. If the examples are not your full set, then I can't read your mind..

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-20 : 10:29:59

what happens or is suppose to happen when your varchar values is NOT a valid date?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-21 : 02:28:43

quote:
Originally posted by goodman2253

it does not return the correct result

What did you mean? Did you get wrong results or error?

Madhivanan

Failing to plan is Planning to fail

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-21 : 08:24:16

hi
if we do with substr then might be it will easy
substr(02022011abcfer,0,2) and match with calender...Its just an idea might be it is possible

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-12-21 : 08:36:30

so what is wrong with my original answer?

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-21 : 10:18:55

its not giving the correct answer..
what should i write in query window
like
select y1 from table

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-12-21 : 11:08:12

You still haven't told us what's wrong with the queries given you. This will work for the sample date you provided, but I'm sure there's something else you're not telling us about the data. Like sometimes the date is in the middle of the string or the end of the string, etc.

I hope this isn't finals week for you!

SELECT * FROM
Table
WHERE ISDATE(SUBSTRING(y1,5,4)+SUBSTRING(y1,3,2)+SUBSTRING(y1,1,2) ) = 1

Jim

Everyday I learn something that somebody else already knew

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-22 : 01:34:40

Hi date will always be at the statrting of the string..

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-22 : 02:24:23

Thnaks.its working now

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources