Converting 2 digit year to 4 digit year

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 2000 Forums

Transact-SQL (2000)

Converting 2 digit year to 4 digit year

Author

Topic

ramecepa
Starting Member

43 Posts

Posted - 2010-07-13 : 22:51:28

Dear All,
How to convert 2 digit to 4 digit year
EX:010100 to 01/01/2000
010109 to 01/01/2009
010158 to 01/01/1958

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-13 : 22:56:45

what is the data type for the 2 digit year date in ?

KH
[spoiler]Time is always against us[/spoiler]

ramecepa
Starting Member

43 Posts

Posted - 2010-07-13 : 23:02:21

string

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-13 : 23:11:46

this will convert it into datetime

select	convert(datetime, stuff(stuff('010158', 3, 0, '/'), 6, 0, '/'), 1)

if you need the end result in string use convert() again into string with the required style

KH
[spoiler]Time is always against us[/spoiler]

ramecepa
Starting Member

43 Posts

Posted - 2010-07-14 : 01:30:54

Thanks Bro

ramecepa
Starting Member

43 Posts

Posted - 2010-07-14 : 05:55:19

Its showing error like
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-14 : 08:40:42

you have data that is not able to convert to a date. Check your data

you can use isdate() to validate the string

KH
[spoiler]Time is always against us[/spoiler]

ramecepa
Starting Member

43 Posts

Posted - 2010-07-28 : 20:54:09

Hi Bro..
it's showing o's and 1's ....any other way??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-28 : 21:37:07

isdate() will return 1 if the date is a valid format.

KH
[spoiler]Time is always against us[/spoiler]

ramecepa
Starting Member

43 Posts

Posted - 2010-07-28 : 21:48:31

Hi,,
KK,,,now how can we convert???

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-28 : 21:59:12

those date are not in valid format. You take a look at those date and ask yourself how to convert

KH
[spoiler]Time is always against us[/spoiler]

ramecepa
Starting Member

43 Posts

Posted - 2010-07-28 : 22:01:02

Hi,,
your correct ,but my requirment like that

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-28 : 22:02:47

if you want to ignore those invalid date, just use the isdate() to validate the date before convert it.

KH
[spoiler]Time is always against us[/spoiler]

ramecepa
Starting Member

43 Posts

Posted - 2010-07-28 : 22:19:32

hi,,
i cannot ignore,when i was used isdate() date function showing below like..
030310--0
032310--1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-28 : 22:23:26

what is your date format ? MMDDYY or DDMMYY ?

KH
[spoiler]Time is always against us[/spoiler]

ramecepa
Starting Member

43 Posts

Posted - 2010-07-28 : 22:29:03

MMDDYY

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-28 : 22:51:21

how do you use isdate ?
you should stuff it first before applying isdate

isdate(stuff(stuff('032310', 3, 0, '/'), 6, 0, '/'))

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-29 : 04:39:31

Make sure to read these
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-iv.aspx

Madhivanan

Failing to plan is Planning to fail

ramecepa
Starting Member

43 Posts

Posted - 2010-08-03 : 23:08:16

Hi Bro...
Thanks for ur information

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources