Converting integer to date time format in sql

I want to convert integer into date format.

Example :
Database 1 : import_date(field)(value is in format ddmmyyyy - e.g 10012003)

Datebase 2 : importdate(field) Format is datetime

I want to store the integer (dateformat) from Database 1 into Database 2 as dd/mm/yyyy.

Again, i have got integer as ddmmyyyy and i want to save it into a database which has format as datetime as dd/mm/yyyy.

Pls urgently reply required.

Need to write stored procedure for this one.


charu isar.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
You need to do it this way:

declare @v int
set @v = 10012003

select @v
select left(cast(@v as char(10)),2) + '/' +
       substring(cast(@v as char(10)),3,2) + '/' +
       substring(cast(@v as char(10)),5,4)    

I have simulated your integer field in my @v variable. If you are using SQL 2000, you could create a UDF with this function.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
radhakrishan1Author Commented:
I have made this procedure :
(@iInteger int)

RETURNS datetime


DECLARE @dDate datetime
if @iInteger <> 0
          set @dDate =cast ( substring(cast(@iInteger as varchar (10)), 3,2)+ '/' + left(@iInteger, 2)+ '/' + right(@iInteger,4)  as datetime)
          set @dDate='01/01/2003'


It gives me an error :

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.

Can u guess what can be the problem.
I am just trying to import from database1 which has one field as an integer into a database which has one field as datetime.
Éric MoreauSenior .Net ConsultantCommented:
use this:
RETURN cast(@dDate as datetime)
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

radhakrishan1Author Commented:
Still gives me an error.
Do u think there would be something wrong with the integer field from where im importing. B'coz its giving an out-of-range datetime value.

If I give to run for 10000 records it runs fine, but when I want it to run for the complete the database it gives me this error.

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.

Should I put some kind of validations on integer which im giving to it.

I have tried this : RETURN cast(@dDate as datetime)
but still the same error.

I think some of the values(integer) in my database may be wrong.

what u say.

Éric MoreauSenior .Net ConsultantCommented:
you will need to validate each value (day, month, year).
radhakrishan1Author Commented:
Thanks a lot brother.
I have a numeric date imported from AS400 in this format: 104125.  It's actually the 125th day of 2004.

How do I convert this in MS SQL to a datetime format?

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.