Thread: Question about Date on Customer DB

I'm inserting a line to the Customer DB through SQL

I'm using the below for the RegisterDate feild

2010-01-05 05:40:57.067

and get the below error, has anyone any idea what I'm doing wrong?

Conversion failed when converting datetime from character string.

What's your insert query?

SET IDENTITY_INSERT [aspdnsf_c4zone].[dbo].[Customer] ON
INSERT INTO [aspdnsf_c4zone].[dbo].[Customer] ([CustomerID], [CustomerGUID], [CustomerLevelID], [RegisterDate], [Email], [Password], [SaltKey], [DateOfBirth], [Gender], [FirstName], [LastName], [Notes], [SkinID], [Phone], [FAX], [AffiliateID], [Referrer], [CouponCode], [OkToEmail], [IsAdmin], [BillingEqualsShipping], [LastIPAddress], [OrderNotes], [SubscriptionExpiresOn], [RTShipRequest], [RTShipResponse], [OrderOptions], [LocaleSetting], [MicroPayBalance], [RecurringShippingMethodID], [RecurringShippingMethod], [BillingAddressID], [ShippingAddressID], [GiftRegistryGUID], [GiftRegistryIsAnonymous], [GiftRegistryAllowSearchByOthers], [GiftRegistryNickName], [GiftRegistryHideShippingAddresses], [CODCompanyCheckAllowed], [CODNet30Allowed], [ExtensionData], [FinalizationData], [Deleted], [CreatedOn], [Over13Checked], [CurrencySetting], [VATSetting], [VATRegistrationID], [StoreCCInDB], [IsRegistered], [LockedUntil], [AdminCanViewCC], [PwdChanged], [BadLoginCount], [LastBadLogin], [Active], [PwdChangeRequired], [RequestedPaymentMethod], [BuySafe])
VALUES ('1', newid(), '0', '2010-01-05 05:40:57.067', 'email', 'pass', '000', '2010-01-07', '', '', '', NULL, '8', '', '', '0', NULL, NULL, '0', '0', '0', '0', NULL, NULL, NULL, NULL, NULL, 'en-US', '0.00', '1', NULL, '1', '1', newid(), '1', '1', NULL, '1', '0', '0', NULL, NULL, '0', '2010-01-05 05:40:57.067', '1', 'USD', '1', '', '1', '1', NULL, '1', '2010-01-07 05:31:38.917', '0', NULL, '1', '0', NULL, NULL)
SET IDENTITY_INSERT [aspdnsf_c4zone].[dbo].[Customer] OFF

Here ya go ^

Just did a copy/paste of your sql, removed the [aspdnsf_c4zone]., and executed the statement. It completed without error. Which version of SQL and which collation are you using?

It turned out to be the dob 2010-01-07 I manually added the date above before testing it!

Thanks for the help!

on a side note what way is the salt and password encoded?

passwords are salted and hashed per Visa requirements, we don't disclose the method or encoding of course. The Web services automation interface (WSI) tool will allow you to input customer and new passwords and have them salted+hashed in bulk if you need.

Thanks I'll have a look at that.

You can also set the SaltKey to -1 when importing directly, and the next time the site resets/restarts the password will be automatically encrypted according to our logic, just as if they'd registered on the account page