Date Conversion in SQL

(0) Share

Report

Posted on by Bill Campbell

22,647

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

All responses (7)

Answers (0)

Beat Bucher GP Gee... 28,021 Super User 2024 Season 1 on at

Like (0)

Report

RE: Date Conversion in SQL

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..
Joseph Markovich 3,900 on at

Like (0)

Report

RE: Date Conversion in SQL

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

:)

Joe
Bill Campbell 22,647 on at

Like (0)

Report

RE: Date Conversion in SQL

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.
Bill Campbell 22,647 on at

Like (0)

Report

RE: Date Conversion in SQL

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.
Joseph Markovich 3,900 on at

Like (2)

Report

RE: Date Conversion in SQL

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
Bill Campbell 22,647 on at

Like (0)

Report

RE: Date Conversion in SQL

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
Kevin Day 610 on at

Like (0)

Report

RE: Date Conversion in SQL

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