change different dates to year only ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

change different dates to year only ?

Author

Topic

Flinx
Starting Member

2 Posts

Posted - 2010-08-03 : 11:08:07

I have a coulum varchar2(200) which contains several thousand dates in 2 Formats DD.MM.YYYY and YYYY:
3.12.1997
11.8.1953
13.5.1988
1945
1.10.2001

I would like to change the whole colum to year only ( YYYY) and idially make it use only a 4 diget number.
I would really apprichiate any help.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-03 : 11:36:48

Here's one way. Not sure if it'll work for all your data. But based on the sample you have provided, this should work.
--Sample Data

create table #t (col1 varchar(200))
insert #t
select '3.12.1997'
union all select '11.8.1953'
union all select '13.5.1988'
union all select '1945'
union all select '1.10.2001'

--Query

update #t
set col1 = parsename(col1,1)

--Result

select * from #t

Flinx
Starting Member

2 Posts

Posted - 2010-08-03 : 14:31:34

When i Try it in oracle sql i get an error @ parsename in step 2
STEP 1
INSERT INTO "H_DATE" ("BVDEP_ID_NUMBER", "DATE_OF_INC")
SELECT
"COMPANIES"."BVDEP_ID_NUMBER", "COMPANIES"."DATE_OF_INC"
FROM "COMPANIES"

Step 2
UPDATE "H_DATE"
SET "H_DATE"."DATE_OF_INC" = PARSENAME("H_DATE"."DATE_OF_INC",1)
ORA-00904: "PARSENAME": invalid identifier

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-03 : 14:32:33

This is a dedicated MS SQL Server Forum. Maybe you will find better help at an Oracle Forum.

Try the one at [url]www.dbforums.com[/url]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-04 : 07:00:24

Try

right(col,4)

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-08-04 : 07:05:36

quote:
Originally posted by Flinx

I have a coulum varchar2(200) which contains several thousand dates in 2 Formats DD.MM.YYYY and YYYY:
3.12.1997
11.8.1953
13.5.1988
1945
1.10.2001

I would like to change the whole colum to year only ( YYYY) and idially make it use only a 4 diget number.
I would really apprichiate any help.

You can use Oracle To_Char function.Something like this

select TO_CHAR(yourcolumn,'YYYY') from your table where len(yourcolumn)>4

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-09 : 02:53:48

quote:
Originally posted by Idera

quote:
Originally posted by Flinx

I have a coulum varchar2(200) which contains several thousand dates in 2 Formats DD.MM.YYYY and YYYY:
3.12.1997
11.8.1953
13.5.1988
1945
1.10.2001

I would like to change the whole colum to year only ( YYYY) and idially make it use only a 4 diget number.
I would really apprichiate any help.

You can use Oracle To_Char function.Something like this

select TO_CHAR(yourcolumn,'YYYY') from your table where len(yourcolumn)>4

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

In ORACLE, you need to use length function

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources