0

I am have been struggling to insert paypal IPN Data into my database. I keep getting the exception: 

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Here is the Stack Trace:

at System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value)
at System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value)
at System.Data.SqlTypes.SqlDateTime..ctor(DateTime value)

I thought it might be choking on dates that are: 1/1/0001, so I changed my DATETIME in SQL to DATETIME2 to no avail.

Also tried to do the following in the application:

private Object SafeDateTime(DateTime Target)
    {
        if (Target < DateTime.MinValue)
        {
            return DBNull.Value;
        }
        else if (Target > DateTime.MaxValue)
        {
            return DBNull.Value;
        }

        return Target;
    }

Here is my table structure:

    CREATE TABLE [dbo].[PayPalPayments](
    [id] [nvarchar](128) NOT NULL,
    [notify_version] [varchar](64) NULL,
    [verify_sign] [varchar](127) NULL,
    [test_ipn] [int] NULL,
    [address_city] [varchar](40) NULL,
    [address_country] [varchar](64) NULL,
    [address_country_code] [varchar](2) NULL,
    [address_name] [varchar](128) NULL,
    [address_state] [varchar](40) NULL,
    [address_status] [varchar](20) NULL,
    [address_street] [varchar](200) NULL,
    [address_zip] [varchar](20) NULL,
    [first_name] [varchar](64) NULL,
    [last_name] [varchar](64) NULL,
    [payer_business_name] [varchar](127) NULL,
    [payer_email] [varchar](127) NULL,
    [payer_id] [varchar](13) NULL,
    [payer_status] [varchar](20) NULL,
    [contact_phone] [varchar](20) NULL,
    [residence_country] [varchar](2) NULL,
    [business] [varchar](127) NULL,
    [item_name] [varchar](127) NULL,
    [item_number] [varchar](127) NULL,
    [quantity] [int] NULL,
    [receiver_email] [varchar](127) NULL,
    [receiver_id] [varchar](13) NULL,
    [custom] [varchar](255) NULL,
    [invoice] [varchar](127) NULL,
    [memo] [varchar](255) NULL,
    [option_name1] [varchar](64) NULL,
    [option_name2] [varchar](64) NULL,
    [option_selection1] [varchar](200) NULL,
    [option_selection2] [varchar](200) NULL,
    [tax] [decimal](10, 2) NULL,
    [auth_id] [varchar](19) NULL,
    [auth_exp] [datetime2](7) NULL,
    [auth_amount] [int] NULL,
    [auth_status] [varchar](20) NULL,
    [num_cart_items] [int] NULL,
    [parent_txn_id] [varchar](19) NULL,
    [payment_date] [datetime2](7) NULL,
    [payment_status] [varchar](20) NULL,
    [payment_type] [varchar](10) NULL,
    [pending_reason] [varchar](20) NULL,
    [reason_code] [varchar](20) NULL,
    [remaining_settle] [int] NULL,
    [shipping_method] [varchar](64) NULL,
    [shipping] [decimal](10, 2) NULL,
    [transaction_entity] [varchar](20) NULL,
    [txn_id] [varchar](19) NULL,
    [txn_type] [varchar](20) NULL,
    [exchange_rate] [decimal](10, 2) NULL,
    [mc_currency] [varchar](3) NULL,
    [mc_fee] [decimal](10, 2) NULL,
    [mc_gross] [decimal](10, 2) NULL,
    [mc_handling] [decimal](10, 2) NULL,
    [mc_shipping] [decimal](10, 2) NULL,
    [payment_fee] [decimal](10, 2) NULL,
    [payment_gross] [decimal](10, 2) NULL,
    [settle_amount] [decimal](10, 2) NULL,
    [settle_currency] [varchar](3) NULL,
    [auction_buyer_id] [varchar](64) NULL,
    [auction_closing_date] [datetime2](7) NULL,
    [auction_multi_item] [int] NULL,
    [for_auction] [bit] NULL,
    [subscr_date] [datetime2](7) NULL,
    [subscr_effective] [datetime2](7) NULL,
    [period1] [int] NULL,
    [period2] [int] NULL,
    [period3] [int] NULL,
    [amount1] [decimal](10, 2) NULL,
    [amount2] [decimal](10, 2) NULL,
    [amount3] [decimal](10, 2) NULL,
    [mc_amount1] [decimal](10, 2) NULL,
    [mc_amount2] [decimal](10, 2) NULL,
    [mc_amount3] [decimal](10, 2) NULL,
    [recurring] [bit] NULL,
    [reattempt] [bit] NULL,
    [retry_at] [datetime2](7) NULL,
    [recur_times] [int] NULL,
    [username] [varchar](64) NULL,
    [password] [varchar](24) NULL,
    [subscr_id] [varchar](19) NULL,
    [case_id] [varchar](28) NULL,
    [case_type] [varchar](28) NULL,
    [case_creation_date] [datetime2](7) NULL,
 CONSTRAINT [PK__PayPalPa__3213E83F001B449F] PRIMARY KEY CLUSTERED

Default values on all but the primary key are NULL.

and my Proc:

    ALTER PROCEDURE [dbo].[AddPayPalTransaction] @id nvarchar(128), @notify_version varchar(64), @verify_sign varchar(127),
  @test_ipn int, @address_city varchar(40), @address_country varchar(64), @address_country_code varchar(2),
  @address_name varchar(128), @address_state varchar(40), @address_status varchar(20), @address_street varchar(200), 
  @address_zip varchar(20), @first_name varchar(64), @last_name varchar(64), @payer_business_name varchar(127),
  @payer_email varchar(127), @payer_id varchar(13), @payer_status varchar(20), @contact_phone varchar(20),
  @residence_country varchar(2), @business varchar(127), @item_name varchar(127), @item_number varchar(127),
  @quantity int, @receiver_email varchar(127), @receiver_id varchar(13),
  @custom varchar(255), @invoice varchar(127), @memo varchar(255), @option_name1 varchar(64),
  @option_name2 varchar(64), @option_selection1 varchar(200), @option_selection2 varchar(200),
  @tax decimal(10, 2), @auth_id varchar(19), @auth_exp datetime2(7), @auth_amount int,
  @auth_status varchar(20), @num_cart_items int, @parent_txn_id varchar(19), @payment_date datetime2(7),
  @payment_status varchar(20), @payment_type varchar(10), @pending_reason varchar(20),
  @reason_code varchar(20), @remaining_settle int, @shipping_method varchar(64), @shipping decimal(10, 2),
  @transaction_entity varchar(20), @txn_id varchar(19), @txn_type varchar(20), @exchange_rate decimal(10,2),
  @mc_currency varchar(3), @mc_fee decimal(10, 2), @mc_gross decimal(10, 2), @mc_handling decimal(10, 2),
  @mc_shipping decimal(10, 2), @payment_fee decimal(10, 2), @payment_gross decimal(10, 2), @settle_amount decimal(10, 2),
  @settle_currency varchar(3), @auction_buyer_id varchar(64), @auction_closing_date datetime2(7), @auction_multi_item int,
  @for_auction bit, @subscr_date datetime2(7), @subscr_effective datetime2(7), @period1 int,
  @period2 int, @period3 int, @amount1 decimal(10,2), @amount2 decimal(10,2), @amount3 decimal(10,2),
  @mc_amount1 decimal(10,2), @mc_amount2 decimal(10,2), @mc_amount3 decimal(10,2), @recurring bit,
  @reattempt bit, @retry_at datetime2(7), @recur_times int, @username varchar(64), @password varchar(24), 
  @subscr_id varchar(19), @case_id varchar(28), @case_type varchar(28), @case_creation_date datetime2(7)
AS
    INSERT INTO PayPalPayments VALUES (@id, @notify_version, @verify_sign,
  @test_ipn, @address_city, @address_country, @address_country_code,
  @address_name, @address_state, @address_status, @address_street, 
  @address_zip, @first_name, @last_name, @payer_business_name,
  @payer_email, @payer_id, @payer_status, @contact_phone,
  @residence_country, @business, @item_name, @item_number,
  @quantity, @receiver_email, @receiver_id,
  @custom, @invoice, @memo, @option_name1,
  @option_name2, @option_selection1, @option_selection2,
  @tax, @auth_id, @auth_exp, @auth_amount,
  @auth_status, @num_cart_items, @parent_txn_id, @payment_date,
  @payment_status, @payment_type, @pending_reason,
  @reason_code, @remaining_settle, @shipping_method, @shipping,
  @transaction_entity, @txn_id, @txn_type, @exchange_rate,
  @mc_currency, @mc_fee, @mc_gross, @mc_handling,
  @mc_shipping, @payment_fee, @payment_gross, @settle_amount,
  @settle_currency, @auction_buyer_id, @auction_closing_date, @auction_multi_item,
  @for_auction, @subscr_date, @subscr_effective, @period1,
  @period2, @period3, @amount1, @amount2, @amount3,
  @mc_amount1, @mc_amount2, @mc_amount3, @recurring,
  @reattempt, @retry_at, @recur_times, @username, @password, 
  @subscr_id, @case_id, @case_type, @case_creation_date)

Here are all the values coming from the sandbox:

@id : abdd363f-2df7-4257-af2e-425b2014873a
@notify_version : 2.1
@verify_sign : AWu5LXYeGjbN4MnftcnMN965XXlkAxXIqlZ1M5Ia2xH6g0yI-POjjt5u
@test_ipn : True
@address_city : San Jose
@address_country : United States
@address_country_code : US
@address_name : John Smith
@address_state : CA
@address_status : Confirmed
@address_street : 123 any street
@address_zip : 95131
@first_name : John
@last_name : Smith
@payer_business_name : 
@payer_email : buyer@paypalsandbox.com
@payer_id : TESTBUYERID01
@payer_status : Verified
@contact_phone : 
@residence_country : US
@business : seller@paypalsandbox.com
@item_name : something
@item_number : AK-1234
@quantity : 1
@receiver_email : seller@paypalsandbox.com
@receiver_id : seller@paypalsandbox.com
@custom : xyz123
@invoice : abc1234
@memo : 
@option_name1 : 
@option_name2 : 
@option_selection1 : 
@option_selection2 : 
@tax : 2.02
@auth_id : 
@auth_exp : 1/1/0001 12:00:00 AM
@auth_amount : 0
@auth_status : None
@num_cart_items : 0
@parent_txn_id : 
@payment_date : 1/1/0001 12:00:00 AM
@payment_status : Pending
@payment_type : Instant
@pending_reason : Other
@reason_code : NotApplicable
@remaining_settle : 
@shipping_method : 
@shipping : 3.04
@transaction_entity : 
@txn_id : 465629582
@txn_type : Web_Accept
@exchange_rate : 0
@mc_currency : US_Dollar
@mc_fee : 0.44
@mc_gross : 12.34
@mc_handling : 0
@mc_shipping : 0
@payment_fee : 0.44
@payment_gross : 12.34
@settle_amount : 0
@settle_currency : US_Dollar
@auction_buyer_id : 
@auction_closing_date : 1/1/0001 12:00:00 AM
@auction_multi_item : 0
@for_auction : False
@subscr_date : 1/1/0001 12:00:00 AM
@subscr_effective : 1/1/0001 12:00:00 AM
@period1 : -1
@period2 : -1
@period3 : 
@amount1 : 0
@amount2 : 0
@amount3 : 
@mc_amount1 : 0
@mc_amount2 : 0
@mc_amount3 : 
@recurring : False
@reattempt : False
@retry_at : 1/1/0001 12:00:00 AM
@recur_times : 0
@username : 
@password : 
@subscr_id : 
@case_id : 
@case_type : None
@case_creation_date : 1/1/0001 12:00:00 AM

Any ideas on what I am missing here because I am really scratching my head.

user2476451
  • 43
  • 5
  • So to confirm: Your stored procedure AND your table AND the code where you add the date as a parameter are all using datetime2? Yes? – ProgrammingLlama Mar 03 '18 at 07:12
  • `SqlDateTime` is C# datatype. `1/1/0001` is not supposed to mean a "valid" or semantically significant date. It might be a NULL date. – Ivan Starostin Mar 03 '18 at 07:30
  • Yes, I updated the code in my question. I had tried switching them back from DATETIME2 to DATETIME to try something. I get same problem with DATETIME and DATETIME2. The proc and the table are datetime2. The C# code is a .Net DateTime object. – user2476451 Mar 03 '18 at 07:45
  • I wrote the dates to a file and get one valid datetime and the rest show this. 1/1/0001 12:00:00 AM – user2476451 Mar 03 '18 at 08:05
  • Can you share the date values for which the query is giving error? – Chetan Mar 03 '18 at 09:13
  • Updated to show all values for query. – user2476451 Mar 03 '18 at 17:06

0 Answers0