help needed in extraction of date from col

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)

help needed in extraction of date from col

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-26 : 05:14:06

Can any one help in writing query to extract a date from col "bdy"

bdy is a col which contains huge data ( apporx one page) which include date also say for eg as below

bdy

---

askjldasdkfjlafj dlasjdklaskfdsjakf when monday, may 05 2008 7:30pm (GMT+5:30)

askjldasdkfjlafj dlasjdklaskfdsjakf may 05 2008 7:30pm (GMT+5:30)

askjldasdkfjlafj dlasjdklaskfdsjakf ladsjfasl aslkjdfasldj askjdfas

on 05 april 2008 this was done

The above 4 records are eg.

What i want is I want to extract only date part of these records and display it in the format mm/dd/yyy

and store it in the temp table along with this col, or even disply it

select col,bdy from item

where

bdy like '

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 05:18:19

will the data be coming only any of 4 above forms?

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-26 : 05:20:01

Thanks Vishak.
No it can be of any form but in between may have date in any form

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 05:21:33

Can there be many dates in a record?

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

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-26 : 05:27:16

Yes there can be many dates

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 05:28:56

Are month names always spelled out or can they be abbreviated?

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

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-26 : 05:29:56

can be abbreviated

Thanks,
aak

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 05:31:44

Ok. Now it is time for you to write down ALL possible combinations of what you treat as a date.
Otherwise we can play this guessing game all week long.

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

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-26 : 05:40:17

Monday,May 05 2008 (GMT +05:30)
4th july 2008
Thursday July 3rd,2008
Thursday, july 03,2008
4th july 2008
Tuesday, july01,2008 11:41AM
Friday, July 11, 2008 9:33AM
25-Oct-2007
10-Jan-2008
may 05 2008 7:30pm (GMT+5:30)
Tuesday 11/16 6:00Pm
Monday, Dec 20, 2:30pm EST

The above type will be sufficient
it will be run only one time to extract the date
and contains less no of record (160)

Thanks for your patience Peso.

aak

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-26 : 06:10:46

I have rechecked it it contains only these type of dates

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 06:10:49

this should give you a start.

;With Your_CTE (string,ID,Val) AS
(
SELECT t.bdy,b.ID,b.Val
FROM YouRtable t
CROSS APPLY dbo.ParseValues(REPLACE(t.bdy,' ',','))b
)

SELECT c1.bdy,
CONVERT(varchar(11),CONVERT(datetime,
CASE WHEN ISDATE(c1.Val)=1 AND LEN(c1.Val)>=8) THEN c1.Val
WHEN ISDATE(c1.Val + ' '+c2.Val + ' '+vl.Valuelist)=1 THEN c1.Val + ' '+c2.Val + ' '+vl.Valuelist
WHEN ISDATE(c1.Val + ' '+vl.Valuelist)=1 THEN c1.Val + ' '+vl.Valuelist
END),101) AS DateAlone
FROM CTE c1
CROSS APPLY (SELECT TOP 1 Val
             FROM CTE
             WHERE ID <c1.ID
             ORDER BY ID DESC)c2
CROSS APPLY (SELECT TOP 2 Val + ' ' AS [text()]
             FROM CTE
             WHERE ID >c1.ID
             ORDER BY ID)vl(valuelist)
WHERE c1.Val LIKE '%[0-9]%'
AND ((ISDATE(c1.Val)=1 AND LEN(c1.Val)>=8)
OR ISDATE(c1.Val + ' '+c2.Val + ' '+vl.Valuelist)=1
OR ISDATE(c1.Val + ' '+vl.Valuelist)=1)

the function parsevalues can be found here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104485

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-26 : 06:37:06

Its giving syntax errors I have created the parsevalues function I will look into it, its taking time as I am not sqlserver background

can you give the simple query just to look into the date in that column and get that date in the format mm/dd/yyyy
excluding repeatative dates etc.

Thanks,
aak.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 06:45:13

quote:
Originally posted by aakcse

Its giving syntax errors I have created the parsevalues function I will look into it, its taking time as I am not sqlserver background

can you give the simple query just to look into the date in that column and get that date in the format mm/dd/yyyy
excluding repeatative dates etc.

Thanks,
aak.

select convert(varchar(11),convert(datetime,datevalue),101)

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-26 : 06:48:32

thanks Visakh,
I tried this

select bdy, convert(varchar(11),convert(datetime,bdy),101) from mytable

its is saying explicit conversion from data type ntext to datetime is not allowed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 06:50:05

quote:
Originally posted by aakcse

thanks Visakh,
I tried this

select bdy, convert(varchar(11),convert(datetime,bdy),101) from mytable

its is saying explicit conversion from data type ntext to datetime is not allowed

ah...so bdy is of type ntext? then parsevalues wont work.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-26 : 06:57:59

oh really then? what can be used instead of that.

Thanks again for your patience Visakh.

Regards,
aak.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-26 : 07:03:13

I will take a backup and change the data type
what data type you want me to have instead of ntext
can I have text?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 07:24:10

quote:
Originally posted by aakcse

I will take a backup and change the data type
what data type you want me to have instead of ntext
can I have text?

nope. use varchar(max) or nvarchar(max)

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-26 : 07:30:50

alter table aak alter column bdy nvarchar(max)

err Incorrect synta near max.

alter table aak alter column bdy nvarchar(4000)

err cannot alter column bdy because it is ntext
table aak has been created but its maximum row size (35681)
exceeds the max no of byte per row 8060 insert update
will fail if the resulting row lenght exceeds 8060

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 07:32:41

quote:
Originally posted by aakcse

alter table aak alter column bdy nvarchar(max)

err Incorrect synta near max.

alter table aak alter column bdy nvarchar(4000)

err cannot alter column bdy because it is ntext
table aak has been created but its maximum row size (35681)
exceeds the max no of byte per row 8060 insert update
will fail if the resulting row lenght exceeds 8060

are you using sql 2000?

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-26 : 07:54:16

yes its sql server 2000

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources