Link to home
Start Free TrialLog in
Avatar of radhakrishan1
radhakrishan1

asked on

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.

regards

charu isar.
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of radhakrishan1
radhakrishan1

ASKER

I have made this procedure :
CREATE FUNCTION udfConvToDate
(@iInteger int)

RETURNS datetime

AS  

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

END

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.
use this:
RETURN cast(@dDate as datetime)
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.

you will need to validate each value (day, month, year).
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?

Thanks