how to convert int to datetime

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

Old Forums

CLOSED - General SQL Server

how to convert int to datetime

Author

Topic

bgjiang
Starting Member

4 Posts

Posted - 2006-08-15 : 10:27:25

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)

7020 Posts

Posted - 2006-08-15 : 10:35:55

What exactly is not working?

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-15 : 10:38:35

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

bgjiang
Starting Member

4 Posts

Posted - 2006-08-15 : 10:47:29

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

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-15 : 10:54:34

bgjiang,

What do u want to do?

Srinika

bgjiang
Starting Member

4 Posts

Posted - 2006-08-15 : 11:01:16

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

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-08-15 : 11:26:55

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).

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-15 : 11:53:31

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

bgjiang
Starting Member

4 Posts

Posted - 2006-08-15 : 12:03:01

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources