Date Conversion Issues - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Date Conversion Issues

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-06-01 : 08:25:39

HI,
I have another message about this, but thought I would createa new one so its easier to follow...

I have a field in my SQL 2000 db table thats is called logdate and has the data type of varchar.
Data looks like : 02/05/2006 06:09:53
( dd/mm/yyyy hh:mm:ss)

If I try to convert it using Convert(datetime,logdate, 113) or
Convert(datetime,logdate,121) or any other variations I receive a server error :

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.

When the date is greater than 12/05/2006

Can anyone help with formatting this so the convert works correctly ?
thank you v much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-01 : 08:41:54

Why did you use varchar datatype to stored dates?
Use proper datetime datatype to avoid this type of problems. You need to have the varchar dates in mm/dd/yyyy format if they are not. Refer this


declare @t table(dates varchar(20))
insert into @t
	select '02/05/2006 06:09:53' union all
	select '12/05/2006 06:09:53' union all
	select '13/05/2006 06:09:53' union all
	select '14/05/2006 06:09:53' union all
	select '02/06/2006 06:09:53' 
select dates as [dd/mm/yyyy],
	substring(dates,4,2)+'/'+left(dates,2)+'/'+substring(dates,7,len(dates)) as [mm/dd/yyyy]
from @t

Madhivanan

Failing to plan is Planning to fail

jamie
Aged Yak Warrior

542 Posts

Posted - 2006-06-01 : 08:55:39

Hi, I didn't develop this database ! It is a db designed by quite a big helpdesk company !
I know, awful design, also I cannot change any of the data in the tables.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-01 : 09:02:00

What do you want to do after converting it to datetime?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-01 : 09:21:28

quote:
Originally posted by jamie

I have a field in my SQL 2000 db table thats is called logdate and has the data type of varchar.
Data looks like : 02/05/2006 06:09:53
( dd/mm/yyyy hh:mm:ss)

If I try to convert it using Convert(datetime,logdate, 113) or
Convert(datetime,logdate,121) or any other variations I receive a server error :

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.

When the date is greater than 12/05/2006

Try

SET DATEFORMAT dmy
GO

DECLARE @datevar datetime
SET @datevar = '13/05/2006'
SELECT @datevar
GO

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-06-01 : 10:03:34

Select convert(datetime, '02/05/2006 06:09:53',103)
Select convert(datetime, '22/05/2006 06:09:53',103)

Srinika

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources