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.
Microsoft SQL Server

Avatar of undefined
Last Comment
cmaxin

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Éric Moreau

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
radhakrishan1

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.
Éric Moreau

use this:
RETURN cast(@dDate as datetime)
ASKER
radhakrishan1

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.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Éric Moreau

you will need to validate each value (day, month, year).
ASKER
radhakrishan1

Thanks a lot brother.
cmaxin

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.