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 2000 Forums

SQL Server Development (2000)

Convert Decimal to DateTime

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-07-14 : 12:20:30

The accounting software we use inserts dates like 20040714 into a decimal column.

I need to be able to convert this to a date/time data type.

When I try
SELECT convert(datetime,ord_dt,101)
FROM OEORDHDR_SQL

I get "Arithmetic overflow" error. What am I doing wrong?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-14 : 12:38:58

Here are some results of conversions.

select convert(decimal, convert(datetime, '7/14/2004'))
--------------------
38180

select convert(datetime, '20040714')
------------------------------------------------------
2004-07-14 00:00:00.000

select convert(datetime, 20040714)
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

select convert(datetime, 38180)
------------------------------------------------------
2004-07-14 00:00:00.000

To convert to datetime you need to have a properly formatted string (2nd query). Or the correct numeric representation of the date (4th query).

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 12:42:52

Perhaps:


SELECT CONVERT(datetime, CONVERT(VARCHAR(8), ord_dt))
FROM OEORDHDR_SQL

Kristen

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-07-14 : 13:03:43

Perfect! You 2 are the best! One of these days I'm going to be a SQL Guru too

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 13:05:50

Me too!

Kristen

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-14 : 13:12:01

Same here.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-14 : 13:41:32

I don't really want to be a SQL Guru. That's so nerdy.

Darnit....where did I put that tape for my commodore?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 13:44:47

Your P.E.T.? Aggghhhh ... I couldn't bare to call it a "Pet" ...

I'm gonna stop posting at 999 honest ....

Kristen-no-nerd

jrockfl
Posting Yak Master

223 Posts

Posted - 2004-07-14 : 14:19:04

Gurus,

I need help again. Here is what I am trying to do. Our local DB is Pervasive and our web server use MS SQL. The dates are stored as decimal data type in Pervasive. Like this 20040714

I'm trying to set up a DTS that will convert the decimal format to date format.

This works great on MS SQL Server, but Pervasive doesnt like it.
SELECT CONVERT(datetime, CONVERT(VARCHAR(8), ord_dt))
FROM OEORDHDR_SQL

Any suggestions for me?

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 14:45:26

DOn't know anything about Pervasive but there is a form of CONVERT called CAST, perhaps you can check the Pervasive manual?
SELECT CAST(CAST(ord_dt as VARCHAR(8)) as datetime)
FROM OEORDHDR_SQL

Maybe Pervasive has a Date datatype, rather than a Datetime?

Kristen

ravencrt
Starting Member

3 Posts

Posted - 2011-01-04 : 07:33:36

Hello
I have a problem and I don't know how to sort it out. Why when I convert a date value to double in access vba I receive a value and when I convert the same data value to decimal in sql I receive a diferent value.

Example:
Access vba
dim data as date
data=date (today is 04/01/2011)
msgbox(cdbl(data))
The resuls of this code is 40547

SQL
set dateformat 'dmy'
seclare @data datetime, @variabila decimal
set @data='04/01/2011'
set @variabila=convert(decimal,@data)
print @variabila
the result returned is 40545

I realy don't understand why is this happening, in my opinion it should return the same result in both cases.

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 08:07:08

There is no standard for this, so VBA is using a different system to SQL.

Maybe you can add "2" to the SQL Value to get a value in VBA, but personally I would not concern myself with the numeric value, used for the internal storage method of the dates, at all.

I would either use "native" format to transfer the dates from VBA to SQL (and let the database transport layer sort it out), or I would transfer them as a String using "yyyymmdd" (no hyphens) format as that will be implicitly converted unambiguously by SQL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-04 : 08:10:41

quote:
Originally posted by ravencrt

Hello
I have a problem and I don't know how to sort it out. Why when I convert a date value to double in access vba I receive a value and when I convert the same data value to decimal in sql I receive a diferent value.

Example:
Access vba
dim data as date
data=date (today is 04/01/2011)
msgbox(cdbl(data))
The resuls of this code is 40547

SQL
set dateformat 'dmy'
seclare @data datetime, @variabila decimal
set @data='04/01/2011'
set @variabila=convert(decimal,@data)
print @variabila
the result returned is 40545

I realy don't understand why is this happening, in my opinion it should return the same result in both cases.

It is becuase in ACCESS default date is Dec 30, 1899 whereas in SQL Server it is Jan 01, 1900

Madhivanan

Failing to plan is Planning to fail

ravencrt
Starting Member

3 Posts

Posted - 2011-01-04 : 08:23:27

Thank you for your replay.
I want to do this because my application works on computers that have different regional settings. My ideea was to write dates in the sql table regardles of the regional settings.

quote:
Originally posted by Kristen

There is no standard for this, so VBA is using a different system to SQL.

Maybe you can add "2" to the SQL Value to get a value in VBA, but personally I could no concern myself with the numeric value of the dates at all.

I would either use "native" format to transfer the dates from VBA to SQL (and let the database transport layer sort it out), or I would transfer them as a String using "yyyymmdd" (no hyphens) format as that will be implicitly converted unambiguously by SQL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-04 : 08:27:42

Have you seen my previous reply?

Madhivanan

Failing to plan is Planning to fail

ravencrt
Starting Member

3 Posts

Posted - 2011-01-04 : 08:35:06

Yes, I was actualy just checking what you wrote. I hope that this is true for all versions of access.
For all version of access (access 2003, 2007 and 2010) the default date is 30/12/1899, because my application is running on coputers that have different versions of office.
For sql I realy don't care because the database is only on sql server 2008.
Thank you very much for your help.

quote:
Originally posted by madhivanan

Have you seen my previous reply?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2011-01-04 : 08:47:58

"For sql I realy don't care because the database is only on sql server 2008"

The DATETIME datatype mechanics have not changed in SQL Server since I can remember (that's SQL 6, 6.5, 7, 2000, 2005, 2008 and 2008R2

)

I can't see it changing in the future either

red75116
Starting Member

1 Post

Posted - 2011-12-01 : 08:49:57

I am having a similar issue that Kristen addresed but my field is formated differently and I am getting the error.

Msg 8115, Level 16, State 5, Line 1
Arithmetic overflow error converting numeric to data type varchar.

In my field the value is actually '20090106.000000' and
either of these give me the error.

SELECT CONVERT(datetime, CONVERT(VARCHAR(8), extra_10))
FROM poordhdr_sql

or

SELECT CAST(CAST(extra_10 as VARCHAR(8)) as datetime)
FROM poordhdr_sql

-red

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-12-01 : 10:27:15

select
	DT,
	DT_Date = convert(datetime,convert(varchar(8),convert(int,dt)))
from
	(  -- Test data
	select DT = 20090106.000000
	) a

Results:

DT               DT_Date
---------------- ------------------------ 
 20090106.000000 2009-01-06 00:00:00.000

(1 row(s) affected)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-02 : 04:40:16

Simpler because of implicit convertion


select
	DT,
	DT_Date = convert(datetime,left(dt,8))
from
	(  -- Test data
	select DT = 20090106.000000
	) a

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources