Converting INT to DATETIME - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Converting INT to DATETIME

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2010-02-03 : 06:55:52

Hi Guys

I'm having a bit of a problem converting integer values to datetime values.

I have a list of integers that resemble the following in a table:
200601
200602
200603
200604
...

I am trying to convert these values to a datetime format with the following code, but it doesn't seem to bringing back the correct value.

SELECT
MonthId
,CONVERT(DATETIME,CONVERT(CHAR(10),MonthId))
FROM Table

Returns:
200601 - 2020-06-01 00:00:00.000
200602 - 2020-06-01 00:00:00.000
200603 - 2020-06-01 00:00:00.000
200604 - 2020-06-01 00:00:00.000

Anyone have any ideas on what I can do?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 06:59:57

[code]select dateadd(mm,(yourcol%100)-1,dateadd(yy,(yourcol/100)-1900,0)) from table[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-03 : 07:20:42

or

select cast(cast(your_col*100+1 as char(8)) as datetime) from your_table

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-02-03 : 07:42:05

Cool solution Madhi..very simple

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2010-02-03 : 07:48:38

Hey

I also found using CONVERT(DATETIME,CONVERT(VARCHAR(10),MonthLastYear_Id) + '01')
does the trick.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-03 : 08:56:10

quote:
Originally posted by Lumbago

Cool solution Madhi..very simple

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Thanks

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-03 : 08:56:58

quote:
Originally posted by rcr69er

Hey

I also found using CONVERT(DATETIME,CONVERT(VARCHAR(10),MonthLastYear_Id) + '01')
does the trick.

It is basically equivalent to my solution
But mine has less code

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources