Sql string to date Conversion

Question

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'

CONVERT supports various styles. This particular format isn't one of them, but you can manipulate the string first so it does match one of these formats (e.g. SELECT CONVERT(DATETIME, REPLACE(REPLACE('Jul 08, 2019 (10:57 AM)', '(', ''), ')', ''), 100)). Do try to change these values once and for all so all future queries can simply use DATETIME for comparison; do not keep this conversion around in queries. — Jeroen Mostert
– Jeroen Mostert, Commented Feb 13, 2020 at 13:47
I knew it was an issue with the parentheses but I didn’t know how to get red of them. Thank you — Yahya
– Yahya, Commented Feb 13, 2020 at 14:44

apomene · Accepted Answer · 2020-02-13 13:48:50Z

1

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

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

answered Feb 13, 2020 at 13:48

apomene

14.4k10 gold badges51 silver badges76 bronze badges

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

Comments

nickfinity · Accepted Answer · 2020-02-13 13:48:00Z

1

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.

answered Feb 13, 2020 at 13:48

nickfinity

1,1192 gold badges15 silver badges31 bronze badges

Collectives™ on Stack Overflow

Sql string to date Conversion

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related