RETRIEVING PROBLEM - 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

RETRIEVING PROBLEM

Author

Topic

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-18 : 21:27:56

insert into newTABLE(newCOLUMN)
Select
Case When [Column 11] like '%DOB%' Then
SubString( [Column 11],
CharIndex('DOB',[Column 11],1)+3,
Case When CharIndex(';',[Column 11],1)= 0 Then
CharIndex('.',[Column 11],1) Else CharIndex(';',[Column 11],1)
End-(CharIndex('DOB',[Column 11],1)+3)) Else '' End,[column 11]
from table

Following is da example of return result

through the image, we can found that some of them got ddmmyy and some got only year.Currently, i want to insert those record to newtable newcolumn with only those got propal date(unwanted for only year).What should i do following?

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-02-18 : 22:12:56

It must be late...I can't quite parse out your statement correctly. But I think you should be able to do something along the lines of:

INSERT INTO newTABLE(newCOLUMN)
SELECT DOB
FROM (Your SELECT statement above with column aliased as "DOB")
WHERE LENGTH(DOB) > 4

Hope that helps!

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-18 : 22:31:07

glad to reveiced ur replied. but error when combining ur script with mine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 23:56:37

quote:
Originally posted by ooi_happiness

glad to reveiced ur replied. but error when combining ur script with mine.

try this:-

SELECT * 
FROM 
(
Your query
)t
WHERE ISDATE(t.Yourdatefield)=1 
AND LEN(t.Yourdatefield) >=10

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-19 : 01:49:45

You should always use proper datatype to store dates
Why did you use text and dates together?

Madhivanan

Failing to plan is Planning to fail

ooi_happiness
Starting Member

22 Posts

Posted - 2008-02-19 : 02:17:51

quote:
Originally posted by visakh16

quote:
Originally posted by ooi_happiness

glad to reveiced ur replied. but error when combining ur script with mine.

try this:-

SELECT *
FROM
(
Your query
)t
WHERE ISDATE(t.Yourdatefield)=1
AND LEN(t.Yourdatefield) >=10

totally solving adi!!!logic of ur example script quite same wif mine!!!
thx a lot!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 08:34:39

quote:
Originally posted by ooi_happiness

quote:
Originally posted by visakh16

quote:
Originally posted by ooi_happiness

glad to reveiced ur replied. but error when combining ur script with mine.

try this:-

SELECT *
FROM
(
Your query
)t
WHERE ISDATE(t.Yourdatefield)=1
AND LEN(t.Yourdatefield) >=10

totally solving adi!!!logic of ur example script quite same wif mine!!!
thx a lot!!!!

You are welcome. Feel free to post whenever you face any problem. cheers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources