The conversion of a char data type to a datetime d

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

The conversion of a char data type to a datetime d

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-10-22 : 16:01:10

Getting the following when trying to run the script below.

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.


insert into bmprdaudes_sql(
item_no,
seq_no,
aud_dt,
aud_tm,
aud_action,
aud_user_name,
aud_terminal_no,
comp_item_no,
alt_item_no,
qty_per_par,
attaching_oper_no,
scrap_factor,
activity_fg,
aud_user_name_defined_field,
effectivity_dt,
obsolete_dt,
mfg_uom,
filler_0005,
loc,
backflush_fg,
bulk_issue_fg,
scrap_qty)
select
item_no,
seq_no,
convert(datetime,convert(char(8),aud_dt)),
convert(datetime,convert(char(8),aud_tm)),
aud_action,
aud_user_name,
aud_terminal_no,
comp_item_no,
alt_item_no,
qty_per_par,
attaching_oper_no,
scrap_factor,
activity_fg,
user_def_fld,
convert(datetime,convert(char(8),effectivity_dt)),
convert(datetime,convert(char(8),obsolete_dt)),
mfg_uom,
filler_0005,
loc,
backflush_fg,
bulk_issue_fg,
scrap_qty
from bmprdaud_sql

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-22 : 16:08:58

You didn't give us much information. So you can't convert some unknown data type to a CHAR(8) and from that to a datetime? Any idea what the datatype is and what values it contains and which might not be valid? Please, provide DDL, DML and expected output. Here are some links that can help:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Vack
Aged Yak Warrior

530 Posts

Posted - 2012-10-22 : 16:38:28

All 4 fields are type int

The tm fields are populated with 185205
The Dt fields are populated with 20120102

I'm looking to have those two converted to datetime

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-22 : 17:04:37

So for the time, does 185205 represent 18:52:05.000 (as in six fifty-two PM)? If not, what does it represent? A time is a not a date. Do you want to convert it to a TIME datatype or do you realy want it to be a DATETIME? If you want a DATETIME, do you care what the DATE portion of the DATETIME is?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-22 : 17:17:35

quote:
Originally posted by Vack

All 4 fields are type int

The tm fields are populated with 185205
The Dt fields are populated with 20120102

I'm looking to have those two converted to datetime

see this

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources