CODEWITHSUNDEEP
sqlsql-server
Yahya
Yahya

Reputation: 240

Sql string to date Conversion

I have inserted a field with a format like this 'Jul 08, 2019 (10:57 AM)'.

now i want to compare dates but this is a string datatype not date, i tried to convert it to date before comparing with date, it fails and gives error i tried Convert function and Cast function

CONVERT(date, 'Jul 08, 2019 (10:57 AM)') Cast('Jul 08, 2019 (10:57 AM)' as date)

is there any way to convert it or to compare it with date like this format 'MM/DD/YYYY'

Upvotes: 0

Views: 565

Answers (2)

apomene
apomene

Reputation: 14389

Your problem are the parenthesis in time. Remove them like:

select convert(DATETIME,  REPLACE(REPLACE('Jul 08, 2019 (10:57 AM)',')',''),'(',''))

Upvotes: 1

nickfinity
nickfinity

Reputation: 1119

The parens are throwing off the conversion. 

SELECT 
FORMAT(CONVERT(date, REPLACE(REPLACE('Jul 08, 2019 (10:57) AM', '(', ''), ')', '')), 'MM/dd/yyyy')
,FORMAT(Cast(REPLACE(REPLACE('Jul 08, 2019 (10:57) AM', '(', ''), ')', '') as date), 'MM/dd/yyyy')
,CONVERT(date, REPLACE(REPLACE('Jul 08, 2019 (10:57) AM', '(', ''), ')', ''))
,Cast(REPLACE(REPLACE('Jul 08, 2019 (10:57) AM', '(', ''), ')', '') as date)

If you need the MM/DD/YYYY format the first two options will work. If you just need the date value for a comparison you can replace the parens.

Upvotes: 1

Related Questions