Change in dateformat while inserting from 1 table to another

Question

I have 2 tables: table 1 and table 2.

One of the columns is the

date

column in table 1, which contains data like:

01/07/2016 00:23:45

I am pulling data from the 1st table to the 2nd table, but the challenge for me is that I am trying to insert this value into 2nd table as:

  07/01/2016 12:23:45 AM

Or at least like:

07/01/2016 00:23:45

Can someone please help me. How can I do this in SQL Server 2008?

Answer 1

Assuming you don't have second in your values you can use below conversion

declare @dt varchar(100) = '01/07/2016 17:23:45'

select LEFT(@dt, 10) + ' ' + replace(right(CONVERT(varchar, cast(@dt as datetime), 109), 13), ':000', ' ')

it will result this

01/07/2016 5:23:45 PM

and for this data '01/07/2016 07:23:45'

will result

01/07/2016 7:23:45 AM

I did not find any straight way using CONVERT function

Change in dateformat while inserting from 1 table to another

Sign up