convert decimal 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

Analysis Server and Reporting Services (2005)

convert decimal to datetime

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-01-09 : 09:40:19

i get column that his type is decimal (the valuse is dates like : 20100601
20110101)

i want to intoduce them as datetime

01-06-2010
01-01-2010

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-09 : 13:08:35

somethinng like

SELECT DATEADD(dd,(yourcol%100)-1,DATEADD(mm,((yourcol%10000)/100)-1,DATEADD(yy,(yourcol/10000)-1900,0))) FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-09 : 16:13:10

SELECT CONVERT(DATETIME, CAST(Col1 AS CHAR(8)), 112)

N 56°04'39.26"
E 12°55'05.63"

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-01-10 : 00:34:10

i want to convert it in reporting services

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 11:36:36

use Cdate() and then apply FormatDatetime function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-01-12 : 04:42:40

can you give example .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-12 : 11:07:15

see

http://www.jackdonnell.com/?p=122

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-01-12 : 13:49:39

i try all the options,but i get #ERROR.
my data is like 20100708 (decimal)

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-01-12 : 13:56:17

i try 2 options but it wrtire me #ERROR

=Formatdatetime(20100708,2)
=Formatdatetime(cdate(20100708),2)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-13 : 11:14:45

try this too
=Formatdatetime(cdate("20100708"),2)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-01-16 : 03:02:13

visakh16 thanks for you help,

but it is not working.

"Conversion from string "20100708" to type 'Date' is not valid"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-18 : 10:16:46

What happens when you use 2010-07-08?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources