| Author |
 Topic  |
|
|
goodman2253
Yak Posting Veteran
88 Posts |
 Posted - 12/20/2011 : 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
United Kingdom
3608 Posts |
Posted - 12/20/2011 : 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.. |
Edited by - RickD on 12/20/2011 09:07:57 |
 |
|
|
goodman2253
Yak Posting Veteran
88 Posts |
Posted - 12/20/2011 : 09:11:37
|
| I have tried very much..but i didn't find the way |
|
|
|
goodman2253
Yak Posting Veteran
88 Posts |
Posted - 12/20/2011 : 09:22:46
|
| it does not return the correct result |
|
|
|
RickD
Slow But Sure Yak Herding Master
United Kingdom
3608 Posts |
Posted - 12/20/2011 : 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 |
|
|
madhivanan
Premature Yak Congratulator
India
22864 Posts |
Posted - 12/21/2011 : 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 - 12/21/2011 : 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
United Kingdom
3608 Posts |
Posted - 12/21/2011 : 08:36:30
|
| so what is wrong with my original answer? |
|
|
|
goodman2253
Yak Posting Veteran
88 Posts |
Posted - 12/21/2011 : 10:18:55
|
its not giving the correct answer..
what should i write in query window
like
select y1 from table |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2875 Posts |
Posted - 12/21/2011 : 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 - 12/22/2011 : 01:34:40
|
| Hi date will always be at the statrting of the string.. |
|
|
|
goodman2253
Yak Posting Veteran
88 Posts |
Posted - 12/22/2011 : 02:24:23
|
| Thnaks.its working now |
|
|
| |
Topic |
|
how to extract date from a string