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.
Amanda WalshawBusiness Solutions AnalsystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent DyerIT Security Analyst SeniorCommented:
Have a look here..

http://sqlfiddle.com/#!3/2e9a0/21

select convert(varchar(13), getdate(), 100) --Sep 13 2013
select convert(varchar, getdate(), 101)  --09/13/2013
select convert(varchar, getdate(), 108)  --05:37:35

select DATEPART(mm,getdate()) -- 9
select Right(100+DATEPART(mm,getdate()),2) -- 09
select DATEPART(dd,getdate()) -- 13
select DATEPART(hh,getdate()) -- 5
select DATEPART(mi,getdate()) -- 30
select DATEPART(ss,getdate()) -- 51

SELECT CAST(DATEPART(hour, getdate()) as varchar(2)) + 
':' +  CAST(DATEPART(minute, getdate())as varchar(2)) --5:38

Open in new window


HTH,

Kent
0
PortletPaulfreelancerCommented:
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: http://www.experts-exchange.com/blogs/PortletPaul/B_7503-SQL-Server-Date-Styles-formats-using-CONVERT.html
0
tigin44Commented:
select convert(varchar(13), BTime, 103),RIGHT(convert(varchar(30), BTime, 0),8)
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

ababu_1982Commented:
Hi,

You are Passing BTime as "1899-12-30 13:15:00.000", But you are Expecting Date column as "25/10/2013". How it is possible?

Or if you trying to extract the output as dd/mm/yyyy format, then below code will help you

select convert(varchar, BTime, 103)  --30/12/1899
SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), BTime, 100), 7)) --1:15PM

Open in new window


Hope This Helps!

-Babu A
0
PortletPaulfreelancerCommented:
@ababu_1982
I had hoped that the use of @ would clearly not be needed in a table, it was used simply as a way for simple proof of the formats

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

would become

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

but, I agree if the field BTime holds values of 1899-12-30 than there has to be some other magic around to arrive at 25/10/2013
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
still experimenting with the time and dates
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.