Date Format Problem - 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

Transact-SQL (2005)

Date Format Problem

Author

Topic

jbosco1988
Starting Member

46 Posts

Posted - 2009-05-11 : 11:40:12

I have a field called IPA_EFFECTIVE_DATE, i am trying to convert them to a regular date but I keep getting the "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value" error

Here is a sample of my dates:

02012007
08202008
11012006
03282001
02012004
09011999
09011999

when tring to convert they come up with this kind of date

9/7/7408
9/8/7408
9/9/7408
9/10/7408
9/11/7408
9/12/7408
9/13/7408

is there any way to fix that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-11 : 11:43:29

Show us the code you tried as that output doesn't make sense.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 11:45:14

whats the datatype of IPA_EFFECTIVE_DATE? also try to pass date values in iso format in queries i.e yyyymmdd

jbosco1988
Starting Member

46 Posts

Posted - 2009-05-11 : 11:51:29

ok this is the code I am using

INSERT INTO My_table
(START_DATE)
SELECT CONVERT(varchar, IPA_EFFECTIVE_DATE, 111) AS Expr1
FROM My_table2

The (START_DATE) is a DateTime field the IPA_EFFECTIVE_DATE is Varchar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 11:57:42

why are you using varchar for storing dates? please try to use appropriate datatype for variables, else you're making date manipulations difficult.

jbosco1988
Starting Member

46 Posts

Posted - 2009-05-11 : 12:00:51

That is the way the imported file is comming over. They are comeing from a .dat file, its like a text file. I am sorry dont mean to make it difficult.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 12:03:45

[code]
INSERT INTO My_table
(START_DATE)
SELECT CONVERT(datetime, STUFF(STUFF(IPA_EFFECTIVE_DATE,3,0,'/'),6,0,'/'), 103) AS Expr1
FROM My_table2
[/code]

jbosco1988
Starting Member

46 Posts

Posted - 2009-05-11 : 12:08:59

Thank you so much!!!! visakh16 you are such and intelligent person. That worked great!!!!

tuesdaysGreen
Starting Member

5 Posts

Posted - 2009-05-11 : 16:44:15

Nice post visakh16! Thanks for the info.

This isn't a huge deal, but I just wanted to add that I think jbosco1988 wants to use the 101 value on the convert function instead of 103 based on the sample dates he provided. I could be wrong though since this is the first time I've used the convert function and may not know what I'm talking about.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources