Date Conversion in SQL

(0) Share

Report

Posted on by Bill Campbell

So to all my sql friends I know this is a simple question, but I am struggling to get it right.

I need to add month or day to the DocDate field in the PM00400 (we are doing work in Excel not on the table)

So I have been trying

select dateadd(month, 1, docdate) from pm00400

and I get a message 'adding a value to a 'datetime' column caused an overflow.

Recognizing that docdate is a datetime field I have tried

CONVERT(VARCHAR(25), CAST(docdate AS date), 107) - that gives me the date as Jan 01, 2021 - workable but not right yet

So I tried to combine the DATEADD and the above line - and I get the same message.

I know I am close, but what am I missing.

Thanks in advance.

Categories:

Microsoft Dynamics GP

I have the same question (0)

All responses (7)

Answers (0)

Sort by

Kevin Day 610 on at

Like (0)

Report
Copy link

Link copied!

Is it as simple as this? You can run this against your database.

SELECT DATEADD(MM, 1, DOCDATE) as [New], DOCDATE as [Old], * FROM PM00400 WHERE DEX_ROW_ID = 1

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report
Copy link

Link copied!

Kevin that works when I do it for 1 or 2 records, but any more and I get 'adding a value to a 'datetime' column caused an overflow'

Is it possible that there is a limit on the number of date converts that can be done in one single pass? It is erroring out at 66340 rows - and there are 159,900 rows in the table

Was this reply helpful? Yes No
Joseph Markovich 4,001 on at

Like (2)

Report
Copy link

Link copied!

Hi Bill-

On a PM00400 table, I did this (there are 101,473 rows):

dateadd("MONTH", 1, convert(date,docdate))

It added a month to all of the rows in a new column successfully. Usually when I am querying the GP data I strip off the time in any of the date fields.

Joe

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report
Copy link

Link copied!

So just for fun, I did some testing and found out that the problem was actually in the DocDate field - one of the Alignment Documents has a date on it of 9999-12-31 and so that fails the system

Good to know we established the end life of this system - the year 9999, Dec 31 - then we can no longer enter a date. I think we better get some of the best minds on this to resolve this 'nearly' pressing matter. Reminds me somewhat of 1999 - but maybe I am just stretching a bit.

Have a good day all.

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report
Copy link

Link copied!

Joe, I do the same, just wondering if I am doing the most effective way.

format(DATEADD(MM, 1, DOCDATE),'yyyy-MM-dd') as [New]

I see we end at the same place - just looking for a 'best or better' way if that is possible.

Was this reply helpful? Yes No
Joseph Markovich 4,001 on at

Like (0)

Report
Copy link

Link copied!

Bill, I have a 12/31/9999 countdown clock in my office.

:)

Joe

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,061 Moderator on at

Like (0)

Report
Copy link

Link copied!

Bill,

Not to be picky.. but does the format really matter ? I mean most of the Excel date fields are driven by the system date settings (be that EN-US or EN-CA, or whatever your country is)..

I know that SQL stores the dates in a Date-Time field type, so unless this comes from a String (text) field, there is no point in trying to convert the date format..

Was this reply helpful? Yes No