convert to date

Question

Post reply

convert to date

Bruin

SSCrazy

Points: 2084
More actions
December 31, 2024 at 3:50 pm
Go to Answer
#4507997

I have a field called DueDates in table where they stored a Date value as Char(10) .
mm/dd/yy
How can I get it to true date so I can perform logic like to find all DueDates > 10/01/2022
I tried cast and convert but when I apply logic DueDates > 10/01/2022 it doesn't find correct output.
Thanks.

pietlinden

SSC Guru

Points: 63516

January 1, 2025 at 5:00 am

Answer

#4508234

FWIW:

I forced the CAST on the text date, so in the outer query, the date values are real dates, so I can order and filter them.

CREATE TABLE textDates(textDate char(10) not null);
go

INSERT INTO textDates(textdate) values ('10/01/2022'),('09/02/2022'),('01/03/2024');

SELECT *
FROM (
SELECT textDate, testDate = CAST(textDate AS DATE)
FROM textDates
) x 
WHERE testDate > '2022-09-03'
ORDER BY testDate ASC

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24422 More actions · Answer 1

You can convert it to a date/datetime with

CONVERT(date, '12/31/24', 1)

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

How to post data/code on a forum to get the best help.

Bruin SSCrazy Points: 2084 More actions · Answer 2

error

Msg 206, Level 16, State 2, Line 1

Operand type clash: date is incompatible with int

pietlinden SSC Guru Points: 63516 More actions · Answer 3

Odd, CONVERT didn't work for me. So I tried it with CAST

CREATE TABLE textDates(textDate char(10) not null);
go

INSERT INTO textDates(textdate) values ('10/01/2022'),('09/02/2022'),('01/03/2024');

SELECT *
FROM (
SELECT textDate, testDate = CAST(textDate AS DATE)
FROM textDates
) x ORDER BY testDate ASC

The wrapper nonsense around the inner select was just so I could test the sorting to see if it really converted to a date.

This reply was modified 2 days, 16 hours ago by pietlinden.

Bruin SSCrazy Points: 2084 More actions · Answer 4

okay thanks... I think problem is when I try to do a where clause against the CAST(textDate AS DATE) .

where CAST(textDate AS DATE) > mm/dd/yy

I get this

Msg 206, Level 16, State 2, Line 1

Operand type clash: date is incompatible with int

DesNorton SSC-Insane Points: 24422 More actions · Answer 5

Maybe your "date" needs quotes

CONVERT(date, textDate , 1) > CONVERT(date, 'mm/dd/yy', 1)

How to post data/code on a forum to get the best help.

DesNorton SSC-Insane Points: 24422 More actions · Answer 6

Without test data we are just guessing.

Maybe this will do the trick

DECLARE @SearchDate date = CONVERT(date, '12/29/2024', 101);

SELECT *
FROM DueDates
WHERE CONVERT(date, textDate , 1) > @SearchDate
ORDER BY CONVERT(date, textDate , 1);

How to post data/code on a forum to get the best help.

Bruin SSCrazy Points: 2084 More actions · Answer 7

I was able to use CAST(textDate AS DATE) into a #temp table and do the date logic from there.

Thanks for all responses.