converting date and time into two columns

Posted on 2013-09-12
Medium Priority
Last Modified: 2013-10-03
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.
Question by:Amanda Walshaw
LVL 17

Assisted Solution

by:Kent Dyer
Kent Dyer earned 800 total points
ID: 39489271
Have a look here..


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


LVL 49

Expert Comment

ID: 39489273
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'

       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
LVL 26

Assisted Solution

tigin44 earned 100 total points
ID: 39489329
select convert(varchar(13), BTime, 103),RIGHT(convert(varchar(30), BTime, 0),8)
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Assisted Solution

ababu_1982 earned 100 total points
ID: 39489379

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

Open in new window

Hope This Helps!

-Babu A
LVL 49

Accepted Solution

PortletPaul earned 1000 total points
ID: 39489451
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

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

would become

       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

Author Closing Comment

by:Amanda Walshaw
ID: 39544826
still experimenting with the time and dates

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question