How do I use SQL CONVERT to convert this pattern to date only?

Question

Here's my SQL:

SELECT * 
FROM MY_TABLE 
WHERE MY_DATE_COLUMN >= CONVERT(DATETIME2, '2022-04-04 15:22:20', 1)

I get this message:

The input character string does not follow style 100, either change the input character string or use a different style.

I've also seen this message at times but haven't determined what causes the different messages:

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

I've also tried at least 10 other style values and none of them work. What is wrong with my SQL?

Does this answer your question? [How to return only the Date from a SQL Server DateTime datatype](https://stackoverflow.com/questions/113045/how-to-return-only-the-date-from-a-sql-server-datetime-datatype) — Ken White, Apr 05 '23 at 04:13
The datetime string format `2022-04-04 15:22:20` is actually style `121` and not `1`. Change to style `121` — Squirrel, Apr 05 '23 at 04:54

Tim Biegeleisen · Accepted Answer · 2023-04-05T04:17:26.777

2

If you let TRY_CONVERT() handle the formatting, it will work:

SELECT TRY_CONVERT(datetime, '2022-04-04 15:22:20');  -- 2022-04-04 15:22:20.000

Demo

If you want a date only, then use:

SELECT TRY_CONVERT(date, '2022-04-04 15:22:20');  -- 2022-04-04

edited Apr 05 '23 at 04:17

answered Apr 05 '23 at 03:41

Tim Biegeleisen

Thanks, but I need to convert the date/time to the date portion only. This solution leaves the time portion attached. – coder Apr 05 '23 at 04:06
Then convert it to a date as a second operation – Dale K Apr 05 '23 at 04:12
Then convert to a date rather than datetime, q.v. the updated answer. – Tim Biegeleisen Apr 05 '23 at 04:18