2

I'm trying to convert a varchar column to date or datetime but I don't understand why it's not possible it should work and I don't see any kind of error.

The column values is 31-07-2017 and the type is Varchar(250). I tried convert and cast and I get the same error:

Conversion failed when converting date and/or time from character string.

Does anyone have any idea on why it's like this?

Improve this question
9
  • 1
    please show us your query Commented Jul 24, 2021 at 11:43
  • 4
    The moral of the story here is to not store dates as text. Commented Jul 24, 2021 at 11:44
  • try this: "SELECT CONVERT(datetime, '2017-08-25');" or you can learn more from here w3schools.com/sql/func_sqlserver_convert.asp Commented Jul 24, 2021 at 11:44
  • 1
    Your date is ambiguous. If you must store a date as something that's not a date, use ISO '20170731' Commented Jul 24, 2021 at 11:45
  • 1
    @AtrinNoori the query was correct the issue was one records in date was really a text thus that's why i had the error it's very strrange how the text was there in firsst place also i check by lenth to see if all the values had the same lenth but it only gives the max lenth for all values any way thank you and it as a very very very amateur mistake Commented Jul 24, 2021 at 13:21

2 Answers 2

Reset to default
6

The conversion error is because your session setting is other than DATEFORMAT dmy or varchar values do not conform to DMY format.

For the latter case, run the query below to identify problem values:

SELECT YourDateColumn AS InvalidDate 
FROM dbo.YourTable 
WHERE
    YourDateColumn IS NOT NULL
    AND TRY_CONVERT(date, YourDateColumn, 103) IS NULL;

As @TimBiegeleisen mentioned in a comment, it is best to choose the most appropriate column type (date in this case) for the data to be stored. Not only will this avoid errors like this, it will improve performance and better ensure data integrity.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Hello, Dan Guzman I already searched for null or space in the columns but there is none i also need to mentionnne i know all of this i tryed to convert to 103 for FR or 105 for ITalien (-) or even US for 1 but it's still same i also tryed to Cast as date but it's really something very strange
If you're still getting errors I'd suggest you have a variety of formats in your column. If so, your data has basically lost all meaning. For example, if you have the value '01-03-2020' you now don't know if it's meant to be 01 March or 03 January.
@OUSSAMABEYGAHAR, did you run the query in my answer?
yes same error i tryed every trick in the book
Hi Laurn, already tryed every possible format
|
2

I found the issue, it's a very very stupid mistake, there was one column with a text format I couldn't see it because I checked with length, but the SQL gives max length so I used group by and manually check dates until I found one column that's not correct in 1 m account records

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.