Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP forum / Date Conversion in SQL
Microsoft Dynamics GP forum
Unanswered

Date Conversion in SQL

(0) ShareShare
ReportReport
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:
Development / Customization / SDK
  • Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,021 Moderator on at
    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 Profile Picture
    Joseph Markovich 3,863 on at
    RE: Date Conversion in SQL

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

    :)

    Joe

  • Bill Campbell Profile Picture
    Bill Campbell 22,647 on at
    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 Profile Picture
    Bill Campbell 22,647 on at
    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 Profile Picture
    Joseph Markovich 3,863 on at
    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 Profile Picture
    Bill Campbell 22,647 on at
    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 Profile Picture
    Kevin Day 610 on at
    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,537 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,520 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans