date conversion and date math - 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)

date conversion and date math

Author

Topic

kennmurrah
Starting Member

13 Posts

Posted - 2007-09-25 : 14:20:48

Greetings.

I have a database with date stored in text format (YYYYMMDD). I need to select the date field and compute number of day from today.

Current query is

SELECT AROPNFIL_SQL.apply_to_no, uv_thomas_outstanding_invoices.amount, AROPNFIL_SQL.curr_doc_dt AS invoice_date

I need to add one more item to the selection, that being the number of days from curr_doc_dt till today.

Any hope would be most appreciated.

Kenn

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 14:24:23

You don't need to do anything fancy since YYYYMMDD is easily converted implicitly.

select datediff(d, curr_doc_dt, getdate()) from yourtable

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

kennmurrah
Starting Member

13 Posts

Posted - 2007-09-25 : 14:37:47

I tried that, but get "arithmetic overflow error converting expression to data type datetime" when I execute the following:

SELECT AROPNFIL_SQL.apply_to_no, uv_thomas_outstanding_invoices.amount, AROPNFIL_SQL.doc_dt, DATEDIFF(d, AROPNFIL_SQL.doc_dt, GETDATE())
AS date_difference
FROM AROPNFIL_SQL INNER JOIN
uv_thomas_outstanding_invoices ON AROPNFIL_SQL.apply_to_no = uv_thomas_outstanding_invoices.apply_to_no

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 14:44:24

Could you post the min and max values of AROPNFIL_SQL.doc_dt?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

kennmurrah
Starting Member

13 Posts

Posted - 2007-09-25 : 14:51:36

SELECT MIN(AROPNFIL_SQL.curr_doc_dt) AS minimumdate, MAX(AROPNFIL_SQL.curr_doc_dt) AS maximumdate
FROM AROPNFIL_SQL INNER JOIN
uv_thomas_outstanding_invoices ON AROPNFIL_SQL.apply_to_no = uv_thomas_outstanding_invoices.apply_to_no

results in 20061107 20070925

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 14:55:44

You must have some data that doesn't fit into a datetime data type.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-25 : 14:58:25

You have a value in the table that is not a valid datetime.

You can use this to search for it:

SELECT
	curr_doc_dt
FROM
	AROPNFIL_SQL
where
	isnull(isdate(curr_doc_dt),0) <> 1

CODO ERGO SUM

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-25 : 14:59:44

kennmurrah -- and now you have just learned why you should always use the correct data types in your tables ....

- Jeff
http://weblogs.sqlteam.com/JeffS

kennmurrah
Starting Member

13 Posts

Posted - 2007-09-25 : 15:00:18

No, I just checked that ... everything seems to be in the right format ...
curr_doc_dt
20070509
20070509
20070509
20070509
20070509
20070511
20070607
20070607
20070613
20070619
20070619
20070626
20070630
20070712
20070724
20070724
20070724
20070731
20070731
20070810
20070810
20070814
20070814
20070814
20070827
20070831
20070831
20070831
20070831
20070831
20070831
20070831
20070831
20070831
20070831
20070831
20070919
20070919
20061107
20070508
20070508
20070508
20070508
20070508
20070508
20070508
20070508
20070511
20070511
20070511
20070511
20070511
20070511
20070511
20070511
20070511
20070511
20070515
20070515
20070516
20070516
20070516
20070516
20070516
20070525
20070525
20070531
20070531
20070531
20070531
20070607
20070607
20070607
20070607
20070607
20070607
20070607
20070607
20070607
20070607
20070607
20070607
20070626
20070626
20070626
20070628
20070628
20070628
20070628
20070628
20070628
20070628
20070628
20070629
20070629
20070629
20070629
20070630
20070630
20070630
20070630
20070630
20070630
20070630
20070630
20070630
20070716
20070716
20070716
20070716
20070718
20070718
20070718
20070718
20070724
20070724
20070724
20070724
20070731
20070731
20070731
20070731
20070810
20070810
20070810
20070814
20070814
20070827
20070827
20070827
20070827
20070827
20070827
20070831
20070831
20070831
20070831
20070831
20070831
20070831
20070831
20070831
20070831
20070831
20070831
20070919
20070919
20070919
20070919
20070919
20070919
20070919
20070919
20070919
20070924
20070924
20070925
20070925
20070925
20070925
20070925
20070925
20070925

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-25 : 15:05:41

So what happened when you ran the query I suggested?

What is the actual datatype for the column?

CODO ERGO SUM

kennmurrah
Starting Member

13 Posts

Posted - 2007-09-25 : 15:07:33

I appreciate all your suggestions and your points are well taken. But in this case, I ran the suggested query and it returned zero rows ...

this query:
SELECT
curr_doc_dt
FROM
AROPNFIL_SQL
where
isnull(isdate(curr_doc_dt),0) <> 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 15:09:49

Put a SET DATEFORMAT YMD first in your query and try again.

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 15:13:16

It works fine for me:

create table table1 (column1 char(8))

insert into table1 values ('20070919')

select datediff(d, column1, getdate())
from table1

drop table table1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-25 : 15:14:12

What is the actual datatype of your column?

Happens when you run this query?


select
	convert(datetime,a.curr_doc_dt),
	datediff(Day,a.curr_doc_dt, GETDATE()) 
from
	(
	select	top 100 percent
		curr_doc_dt
	from
		AROPNFIL_SQL
	where
		isnull(isdate(curr_doc_dt),0) = 1
	) a

CODO ERGO SUM

kennmurrah
Starting Member

13 Posts

Posted - 2007-09-25 : 15:17:57

Okay, first of all, thanks to all of you for your help. Michael, your last post caused me to question my assumptions that it was a text field. Upon checking, I found an integer, instead. So the following worked:

SELECT AROPNFIL_SQL.apply_to_no, uv_thomas_outstanding_invoices.amount, AROPNFIL_SQL.doc_dt,
DATEDIFF(d, convert(char(8),AROPNFIL_SQL.doc_dt), GETDATE())
AS date_difference
FROM AROPNFIL_SQL INNER JOIN
uv_thomas_outstanding_invoices ON
AROPNFIL_SQL.apply_to_no = uv_thomas_outstanding_invoices.apply_to_no

once again, thanks to all of you.

Kenn

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-26 : 00:38:39

Wow... all that just because someone didn't say what the datatype of the column was...

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-26 : 02:19:02

quote:
Originally posted by Michael Valentine Jones

What is the actual datatype of your column?

Happens when you run this query?

select
convert(datetime,a.curr_doc_dt),
datediff(Day,a.curr_doc_dt, GETDATE())
from
(
select top 100 percent
curr_doc_dt
from
AROPNFIL_SQL
where
isnull(isdate(curr_doc_dt),0) = 1
) a

CODO ERGO SUM

Like ISNUMERIC(). ISDATE() also should be dealt with care


SELECT 
       ISDATE(2007),ISDATE('2007'),ISDATE('2007-10-10'),ISDATE(2007-10-10)

If the values are stored in YYYYMMDD format, you need not only to use ISDATE() but LEN() as well

Select .........
where ISDATE(col)=1 and len(col)=8

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 02:47:03

quote:
Originally posted by Jeff Moden

Wow... all that just because someone didn't say what the datatype of the column was...

He did

quote:
Originally posted by kennmurrah

I have a database with date stored in text format (YYYYMMDD).

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-26 : 02:55:27

quote:
Originally posted by Peso

quote:
Originally posted by Jeff Moden

Wow... all that just because someone didn't say what the datatype of the column was...
He did
quote:
Originally posted by kennmurrah

I have a database with date stored in text format (YYYYMMDD).

E 12°55'05.25"
N 56°04'39.16"

But it sounded that OP used char or varchar datatype than Integer

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-26 : 09:32:06

quote:
Originally posted by madhivanan

quote:
Originally posted by Peso

quote:
Originally posted by Jeff Moden

Wow... all that just because someone didn't say what the datatype of the column was...
He did
quote:
Originally posted by kennmurrah

I have a database with date stored in text format (YYYYMMDD).

E 12°55'05.25"
N 56°04'39.16"

But it sounded that OP used char or varchar datatype than Integer

Madhivanan

Failing to plan is Planning to fail

Sometimes, you have to be like a detective and start cross examining them when their story doesn’t hold together.

CODO ERGO SUM

johnsql
Posting Yak Master

161 Posts

Posted - 2007-09-27 : 11:13:28

quote:
Originally posted by kennmurrah

Okay, first of all, thanks to all of you for your help. Michael, your last post caused me to question my assumptions that it was a text field. Upon checking, I found an integer, instead. So the following worked:

SELECT AROPNFIL_SQL.apply_to_no, uv_thomas_outstanding_invoices.amount, AROPNFIL_SQL.doc_dt,
DATEDIFF(d, convert(char(8),AROPNFIL_SQL.doc_dt), GETDATE())
AS date_difference
FROM AROPNFIL_SQL INNER JOIN
uv_thomas_outstanding_invoices ON
AROPNFIL_SQL.apply_to_no = uv_thomas_outstanding_invoices.apply_to_no

once again, thanks to all of you.

Kenn

I have a question here, why converting the column to the type of VACHAR(8) solves the problem while to datetime type doesn't?
Thanks.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources