Conversion failed when converting datetime from character string.

Question

Conversion failed when converting datetime from character string.

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 1

Susan,

You list 06-30-2007 and 2007-10-10 as the format.

Not sure if this will help as it is an answer that has only been checked for SQL Server 2008. (not to mention it is after the fact - but I found your post) I added the time as well just to extend the metaphor a bit.

declare @date datetime

select cast(CONVERT(varchar(30),'20070630 23:00',130) as DATETIME)

set @date =CONVERT(varchar(30),'20070630 23:00',130)

select @date

SELECT CONVERT(datetime,@date)

select CONVERT(decimal(13,4),CONVERT(datetime,@date))

You get several answers in this manner - including a one I find particularly appealing - storing the date in decimal format (Please note it is only accurate to the nearest 400 ns).

There is an 8601 ISO standard that may help to alleviate the formatting somewhat - you may want to check this as well.

http://technet.microsoft.com/en-us/library/ms180878.aspx

Jamie

zyzy SSC Journeyman Points: 88 More actions · Answer 2

Take a good look at your table columns, you might be converting a non date type to date datatype

Phil Parkin SSC Guru Points: 246990 More actions · Answer 3

zyzy (9/4/2012)
Take a good look at your table columns, you might be converting a non date type to date datatype

You just answered a post which is three years old... 🙂

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 4

Phil Parkin (9/5/2012)
zyzy (9/4/2012)
Take a good look at your table columns, you might be converting a non date type to date datatype
You just answered a post which is three years old... 🙂

If it's the right answer, does it matter?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)