Date time conversion error when converting varchar date format in SQL Server

0
votes

I want to convert this date time format '01/02/2019 12:00:00 AM' to
'2019-02-01' format.

I have written this code:

declare @start_date datetime;
declare @end_date datetime;

set @start_date = '01/02/2019 12:00:00 AM';
set @end_date = '28/02/2019 12:00:00 AM';

select * 
from test 
where [Global Dimension 1 Code] in ('FIN') 
  and convert(char(10), [Posting Date], 126) between convert(date, convert(char(10), @start_date, 126), 103)  
                                                 and convert(date, convert(char(10), @end_date, 126), 103)

But I'm getting an error

Msg 242, Level 16, State 3, Line 8
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Msg 241, Level 16, State 1, Line 10
Conversion failed when converting date and/or time from character string.

but when I have run the query as below It was executed without an error.

select * 
from test 
where [Global Dimension 1 Code] in ('FIN') 
  and convert(char(10), [Posting Date], 126) between convert(date, convert(char(10), '01/02/2019 12:00:00 AM', 126), 103) 
                                                 and convert(date, convert(char(10), '28/02/2019 12:00:00 AM', 126), 103)
2
sqlsql-servertsql

2 Answers

1
votes

use 103

SELECT convert(datetime, '01/02/2019 12:00:00 AM', 103)

or

SELECT convert(char(10), '01/02/2019 12:00:00 AM', 126)
1
votes

use below with cast() and format() - will work sql server 2012+

DEMO

select format(cast('01/02/2019 12:00:00 AM' as date),'yyyy-MM-dd')