shan shan - 7 months ago 20
SQL Question

Change in dateformat while inserting from 1 table to another

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

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