CODEWITHSUNDEEP
sql-servert-sqldatetime
Avinash
Avinash

Reputation: 305

Converting String to Datetime2

I am trying to convert 

@string='25/05/2016 09:00'

to 2016-05-25 09:00.

@string is concatenation of @string2='25/05/2016' and @string3='09:00'

When I try to do this using 

CONVERT(datetime, '25/05/2016 09:00')

I get the following error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Please help, thanks.

Upvotes: 12

Views: 37412

Answers (3)

Avinash
Avinash

Reputation: 305

thanks marc_s for putting my query in the right format.

I tried this and got the expected result, please advise if there is any other optimal way, thanks

 Declare @string varchar(20)='25/05/2016'
 Declare @string2 varchar(20)='09:00'
 Declare @string3 Varchar(20)=(SELECT Right(@string,4)+'-'+SUBSTRING(@string,4,2)+'-'+LEFT(@string,2)+' '+@string2)
 Select CONVERT(datetime,@string3) as _datetime

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

Try this:

SELECT CONVERT(datetime2, '25/05/2016 09:00', 103)

The convert method takes 3 arguments: The first is the target data type, the second is the expression to convert, and the third is the style. In this case, 103 stands for British or French date format, which is dd/mm/yyyy.

Declare @string char(10)='25/05/2016'
Declare @string2 char(5)='09:00'

SELECT CONVERT(datetime2, @string + ' ' + @string2, 103)

Result: 2016-05-25 09:00:00.0000000 (datetime2)

Upvotes: 20

Chanom First
Chanom First

Reputation: 1136

add this statement before convert 

SET DATEFORMAT DMY

so query look like this 

SET DATEFORMAT DMY

SELECT CONVERT(datetime, '25/05/2016 09:00')

Upvotes: 0

Related Questions