Getting Error while convert varchar into datetime

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

Getting Error while convert varchar into datetime

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2009-02-02 : 03:14:17

Hi Friends,

I Am gotting the Error When i Convert the varchar value into datetime.I give The details as follows.Please help me to find the solution.

Field Value =Sep 7 2007 12:

SQL Query =
select top 10 convert(varchar(10),convert(datetime,(convert(varchar(10),invoicedate)),101)) from invoice

Output=
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.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-02 : 03:25:44

ur query working fine once check it

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-02 : 03:27:23

Try this also

select convert(varchar(20),convert(datetime,(convert(varchar(10),'Sep 7 2007 12')),101))

check it once, length is not sufficient for varchar(10)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-02 : 03:27:57

u should follow the format yyyy/mm/dd or mm/dd/yyyy

u invoicedate having dd/mm/yyyy format so that ur getting that error check this once
declare @d datetime
set @d = '14/6/2008'

select @d, convert(varchar(32),@d)--- ur will get error

declare @dd datetime
select @dd = '6/14/2008'

select @dd, convert(varchar(32),@dd)-- u will get required output

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-02 : 03:28:15

Hi i think this is enough

select convert(varchar(11),fieldname,101)

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 03:58:43

why are you using varchar for storing datevalues? it will really make date manipulations complex.

vidhya
Posting Yak Master

108 Posts

Posted - 2009-02-02 : 04:29:31

Hi all,

I am given the sample input value,i need to update a table(invoice) with 3000 records.

still i am getting the same error,while i try your suggesstion.
please look at this.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-02 : 04:32:46

In What format(style) the date is stored in ur table

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 04:38:06

how does your date format exists? is it consistent?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources