SQL Data Conversion Failed DateTime

Question

I have a update query as follows

UPDATE EcommerceCustomerActiveSites SET SubscriptionExpDate = '19/08/2021 12:15:17 PM', IsEnabled = 1 WHERE ContactCode = 'CCTC-002833'

This gives me an error as follows

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

However. when I do a select query from the same table and copy a sample value from the SubscriptionExpDate column, it returns the same format as my insert statement!

19/08/2021 12:27:06 PM  -- Value From Select
19/08/2021 12:15:17 PM  -- Value From Insert

Does anyone have any clues?

Answer 1

Just use a more appropriate value for the constant string:

UPDATE EcommerceCustomerActiveSites 
  SET SubscriptionExpDate = '2012-08-19 12:15:17',
      IsEnabled = 1 
  WHERE ContactCode = 'CCTC-002833';

The format for datetime constants is described in the documentation.