February 13, 2019 at 2:49 am
I have CREATE TABLE myTable
(
UpdateDate VARCHAR(30)
)
INSERT myTable
VALUES ('17/01/2019')
And I have this query SELECT
DateColumn = CAST(UpdateDate AS DATE)
FROM myTable
SELECT
DateColumn = CONVERT(DATE, UpdateDate)
FROM myTable
None of the query is working, I'm getting this error Conversion failed when converting date and/or time from character string.
where am I getting it wrong?
February 13, 2019 at 3:29 am
mediacommentry - Wednesday, February 13, 2019 2:49 AMI haveCREATE TABLE myTable
(
UpdateDate VARCHAR(30)
)
INSERT myTable
VALUES ('17/01/2019')And I have this query
SELECT
DateColumn = CAST(UpdateDate AS DATE)
FROM myTableSELECT
DateColumn = CONVERT(DATE, UpdateDate)
FROM myTableNone of the query is working, I'm getting this error
Conversion failed when converting date and/or time from character string.
where am I getting it wrong?
Your #1 problem is the storing of dates as varchar. If possible, change it.
So, in order to convert all of the text to dates, you need to know the format of the dates being converted.
Are ALL of the dates in the format "dd/mm/yyy"? If yes, then this should workSELECT
DateColumn = CONVERT(DATE, UpdateDate, 103)
FROM myTable
You can read more about the styles on the CAST and CONVERT page
February 13, 2019 at 9:26 am
You can also use TRY_CAST() or TRY_CONVERT() to identify which records have issues. Each of these will produce a NULL value instead of an error when the conversion fails.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply