In Sql server :

I have a field start_date in Table1 (2 values given below)

'2006-08-15 00:00:00.000',

'2010-07-13 18:53:59.000'

I want to convert these dates in format "yyyy-mm-dd'T'hh:mm:ss.SSS'Z'"

I have tried both of the following things

1. Select Convert(varchar,start_date,126) 'Z' as required_date from Table1

2. Select Convert(varchar,start_date,127) 'Z' as required_date from Table1

And it giving result as

'2006-08-15T00:00:00Z'

'2010-07-13 18:53:59Z'

Last 000 is getting trimmed out (That should not happen).

I want result like

'2006-08-15T00:00:00.000Z'

'2010-07-13 18:53:59.000Z'

How can I achieve this? Any help would be appreciated.

Thanks.

CodePudding user response：

I'm new to sql but I assume that the removal of .000 is because of sql itself not showing milliseconds or whatever that is. Try adding '.000Z' where you add 'Z' and you should have it?

Should look like this

Select Convert(varchar,start_date,126) '.000Z' as required_date from Table1

Select Convert(varchar,start_date,127) '.000Z' as required_date from Table1

CodePudding user response：

You can use below

Select Convert(varchar(25),start_date,121) 'Z' as required_date from Table1

Some good explanation here SQL Convert Date functions and formats