SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 how to extract date from a string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

goodman2253
Yak Posting Veteran

86 Posts

Posted - 12/20/2011 :  08:56:00  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 12/20/2011 :  09:05:46  Show Profile  Reply with Quote
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..

Edited by - RickD on 12/20/2011 09:07:57
Go to Top of Page

goodman2253
Yak Posting Veteran

86 Posts

Posted - 12/20/2011 :  09:11:37  Show Profile  Reply with Quote
I have tried very much..but i didn't find the way
Go to Top of Page

goodman2253
Yak Posting Veteran

86 Posts

Posted - 12/20/2011 :  09:22:46  Show Profile  Reply with Quote
it does not return the correct result
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 12/20/2011 :  10:10:23  Show Profile  Reply with Quote
It returns correctly for the examples you gave. If the examples are not your full set, then I can't read your mind..
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 12/20/2011 :  10:29:59  Show Profile  Reply with Quote
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/


Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 12/21/2011 :  02:28:43  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

goodman2253
Yak Posting Veteran

86 Posts

Posted - 12/21/2011 :  08:24:16  Show Profile  Reply with Quote
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
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 12/21/2011 :  08:36:30  Show Profile  Reply with Quote
so what is wrong with my original answer?
Go to Top of Page

goodman2253
Yak Posting Veteran

86 Posts

Posted - 12/21/2011 :  10:18:55  Show Profile  Reply with Quote
its not giving the correct answer..
what should i write in query window
like
select y1 from table
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/21/2011 :  11:08:12  Show Profile  Reply with Quote
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
Go to Top of Page

goodman2253
Yak Posting Veteran

86 Posts

Posted - 12/22/2011 :  01:34:40  Show Profile  Reply with Quote
Hi date will always be at the statrting of the string..
Go to Top of Page

goodman2253
Yak Posting Veteran

86 Posts

Posted - 12/22/2011 :  02:24:23  Show Profile  Reply with Quote
Thnaks.its working now
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000