SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 how to convert int to datetime
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

bgjiang
Starting Member

4 Posts

Posted - 08/15/2006 :  10:27:25  Show Profile
Hello:

I have a problem to convert int number to datetime format.

what I am trying to do is: get all data where the difference between clc_service_from_date and index_start_date is 0 and -4 months. the index_start_date has the same format as clc_service_date which is int (sample data: 20040506, 20060225, 20051231)

my code :

datediff(month, cast(convert(char(8), clc_service_from_date) as datetime), cast(convert(char(8), index_start_date) as datetime) ) between 0 and -4

it's not working, So would someone have experience in Microsoft sql help me with this problem.

thank you,

Baogong

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 08/15/2006 :  10:35:55  Show Profile
What exactly is not working?

CODO ERGO SUM
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 08/15/2006 :  10:38:35  Show Profile  Send madhivanan a Yahoo! Message
Why did you use int datatype to stored dates? Use proper datetime datatype to avoid convertions
Do you want to get last 4 month's data?


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

bgjiang
Starting Member

4 Posts

Posted - 08/15/2006 :  10:47:29  Show Profile
the error is:
RROR: Open cursor error: ICommand::Execute failed. : Arithmetic overflow error for type varchar, value = 20051230.000000.

The real value for clc_service_from_date here is 20051230 format int, I did not know why after convert or cast, it's value change to 20051230.000000. please help.


I did not know why they defined clc_service_from_date as int instead of datetime format. but now, I need convert it to datetime format and select data that 4 months older than intake_index_date (same format as clc_service_from_date).

Thanks for help,

baogong
Go to Top of Page

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 08/15/2006 :  10:54:34  Show Profile
bgjiang,

What do u want to do?


Srinika
Go to Top of Page

bgjiang
Starting Member

4 Posts

Posted - 08/15/2006 :  11:01:16  Show Profile
What do u want to do?

I want convert clc_service_from_date int format to datetime format.

table clc:
clc_service_from_date new_datetime
20051230 (int format) datetime format
20051231 (int format) datetime format

thank you,
baogong


Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 08/15/2006 :  11:26:55  Show Profile
It's obviously not a *good* idea to store a date as an integer in the form yyyymmdd since any operation other than equality and collation will require you to convert it, but what you wrote will almost work on integers. The only thing I can see wrong is that you have to specify BETWEEN <low> AND <high> or you won't get any results!

SELECT *
FROM (
    SELECT CAST(20040506 AS int) AS clc_service_from_date,
      CAST(20060225 AS int) AS index_start_date
    UNION ALL SELECT 20051231, 20050801
    UNION ALL SELECT 20060101, 20060131
  ) AS A
WHERE datediff(month, cast(convert(char(8), clc_service_from_date) as datetime),
  cast(convert(char(8), index_start_date) as datetime)) between -4 and 0

This will return, as expected, the second and third row.

But from the error you're getting, I'd say you had a Numeric column with a non-zero scale rather than an Int column. In that case, you'd have to convert it to an Int first (and wonder what non-integer values mean).

Edited by - Arnold Fribble on 08/15/2006 11:29:43
Go to Top of Page

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 08/15/2006 :  11:53:31  Show Profile
quote:
Originally posted by bgjiang


I want convert clc_service_from_date int format to datetime format.

table clc:
clc_service_from_date new_datetime
20051230 (int format) datetime format
20051231 (int format) datetime format



While agreeing with all the others who insist on "USE DATETIME for DATE Data"



Create table #t (clc_service_from_date int, new_datetime datetime)
Insert into #t values (20051230, Null)
Insert into #t values (20051231, Null)

Update #t set new_datetime = convert(datetime,convert(varchar(20),clc_service_from_date),112)

Select * from #t


Srinika
Go to Top of Page

bgjiang
Starting Member

4 Posts

Posted - 08/15/2006 :  12:03:01  Show Profile
Thank you everyone for the help.

I have to convert the clc_service_from_date into int first and then my code works. so the clc_service_from_date is not int format although it look is.

here is the working code:
datediff(month,cast(convert(char(8),convert(int,clc_service_from_date)) as datetime),cast(convert(char(8),convert(int,intake_start_date)) as datetime))


thanks again for help,
baogong
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000