0

I need to change a DateTime to a new format by appending zeros to dates less than 10. DateTime column is nvarchar(MAX)

Currently: - April 8 2018
4/8/2018 00:00:00 AM

New wanted format:

4/**08**/2018 00:00:00 AM
2

Do not store dates and times as strings like nvarchar while the database has native datatypes like datetime. These are not saved as strings but in an internal binary format designed for storing this type of information.

Such datatype saves the exact information instead of some of its various representations, allowing you to get it in different formats, making the conversion needed in this question unnecessary. More than that, it allows doing calculations and comparisons using database functions.

0

The following SQL Server solution uses the FORMAT command. It also assumes all of the nvarchar DateTime values are midnight.

DECLARE @OriginalTable TABLE (DateVarChar NVARCHAR(max))

INSERT INTO @OriginalTable (DateVarChar)
VALUES ('4/8/2018 00:00:00 AM')
    ,('4/9/2018 00:00:00 AM')
    ,('4/10/2018 00:00:00 AM')

UPDATE @OriginalTable
SET DateVarChar = FORMAT(convert(DATETIME, DateVarChar), 'M/dd/yyyy 00:00:00', 'en-US') + ' AM'

SELECT *
FROM @OriginalTable

| DateVarChar           |
|-----------------------|
| 4/08/2018 00:00:00 AM |
| 4/09/2018 00:00:00 AM |
| 4/10/2018 00:00:00 AM |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.