convertion datetime error - 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 2005 Forums

SQL Server Administration (2005)

convertion datetime error

Author

Topic

arunsqladmin
Yak Posting Veteran

74 Posts

Posted - 2008-01-29 : 00:09:59

i am getting error while convering datetime datatype using

convert(x,101)
function

Arithmetic overflow error converting expression to data type datetime

please help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-29 : 00:15:46

what is the format of your datetime string ?

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

arunsqladmin
Yak Posting Veteran

74 Posts

Posted - 2008-01-29 : 00:19:12

select convert(varchar(20),convert(datetime,validity),107) as validity, fileno, name, country,program, supervisor, phoneno, category from apprentices where category='Apprentice'and convert(datetime, validity) > getdate()

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-29 : 00:27:23

try

convert(datetime, validity, 107) > getdate()

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

arunsqladmin
Yak Posting Veteran

74 Posts

Posted - 2008-01-29 : 00:33:53

i tried even that , it is not working

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-29 : 00:36:23

Can you provide sample data on which you are running your query? Perhaps that may help in solving your problem.

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-29 : 00:40:49

quote:
Originally posted by khtan

what is the format of your datetime string ?

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

check your data in the validity field. There are some rows that of different format

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

arunsqladmin
Yak Posting Veteran

74 Posts

Posted - 2008-01-29 : 01:03:40

1)
SELECT CONVERT(varchar(20), CONVERT(datetime, Validity), 101) AS validity, Fileno, Name, Country, Program, Supervisor, Phoneno, category
FROM Apprentices
WHERE (category = 'Research Fellow') AND (CONVERT(datetime, Validity) > GETDATE())

2)
SELECT CONVERT(varchar(20), CONVERT(datetime, Validity), 101) AS validity, Fileno, Name, Country, Program, Supervisor, Phoneno, category
FROM Apprentices
WHERE (category = 'apprentice') AND (CONVERT(datetime, Validity) > GETDATE())

first one works but the second one does not work..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-29 : 01:15:59

it means you have datetime in string for category = 'apprentice' that is not in proper 101 format

try
select Validity, Fileno, Name
FROM Apprentices
WHERE category = 'apprentice'
and isdate(Validity) = 0

to identify the rows with invalid date format in string.

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

arunsqladmin
Yak Posting Veteran

74 Posts

Posted - 2008-01-29 : 01:34:50

select Validity, Fileno, Name
FROM Apprentices
WHERE category = 'apprentice'
and isdate(Validity) = 0

there is one record which shows invalid format ,,, what should i do next

arunsqladmin
Yak Posting Veteran

74 Posts

Posted - 2008-01-29 : 01:46:46

i have solved the issue ,, u r a genius Mr.KH.. hats off to u

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-29 : 04:39:38

1 Always use proper DATETIME datatype to store dates
2 Handle ISDATE() with care http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources