asked on

converting date and time into two columns

I am in the process of writing a view, i need to convert this field
BTime = 1899-12-30 13:15:00.000

to 25/10/2013 (column 1)
1.15pm (column 2)

i have used the following without success
for Date
convert(varchar(13), BTime, 100)
for time
convert(varchar, BTime, 100)

1.15pm is correct on on the date but the dates is not correct
and how do you hide the date for time.

SOLUTION

Kent Dyer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

PATTERN      STYLED DATE                SYNTAX                                  STYLE
MMM DD YYYY  Feb 23 2001 4:05AM         convert(varchar, your_data_here ,100)   100  
DD MM YYYY   23/02/2001                 convert(varchar, your_data_here ,103)   103

Open in new window

to 25/10/2013 (column 1)
1.15pm (column 2)

DECLARE @BTime AS datetime = '1899-12-30 23:15:00.000'

SELECT
       convert(varchar(10), @BTime, 103)
     , ltrim(right(convert(varchar, @BTime, 100),7))

Open in new window

also see: https://www.experts-exchange.com/blogs/PortletPaul/B_7503-SQL-Server-Date-Styles-formats-using-CONVERT.html

SOLUTION