CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Red Devil
Red Devil

Reputation: 2393

Date time conversion issue in sql server

I have this query:

declare @a datetime = '20/05/2019 9:22:07'

I want output as:

2019-05-20 9:22:07

I tried :

SELECT CONVERT(CHAR(10),CONVERT(DATETIME,LEFT(@a,10),103),101);

SELECT convert(varchar, convert(date, @a, 105), 101)

But it gives me an error:

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

How to convert this type of data?

I want to get the standard date time conversion which will work in all languages

Upvotes: 0

Views: 172

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Your conversion problem is occurring here:

declare @a datetime = '20/05/2019 9:22:07';

I think you intend this as a string:

declare @a varchar(255) = '20/05/2019 9:22:07';

To get the time with only one digit for the hour, you can use:

SELECT CONVERT(VARCHAR(10), CONVERT(DATETIME, LEFT(@a, 10), 103), 120) + ' ' + TRIM(RIGHT(@a, 8))

More commonly, you would use:

SELECT CONVERT(VARCHAR(20), CONVERT(DATETIME, @a, 103), 120)

This produces:

2019-05-20 09:22:07

Upvotes: 2

Related Questions