Convert datetime to only time format in 1 column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert datetime to only time format in 1 column

Author

Topic

notes4we
Yak Posting Veteran

90 Posts

Posted - 2008-09-25 : 15:20:33

Hello Friends,

I have a table named Table1 and a column named CreationTime.
CreationTime has the datatype as datetime.
It gives the output as mm/dd/yyyy hh:mm:ss
But I want the output as hh:mm:ss
I am aware that I can use CAST or CONVERT function, but I am not getting a proper syntax for the same.
I would appreciate if anyone of you could help me to get the current syntax for what I wish to achieve.

Thank you,
notes4we

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-25 : 15:24:46

If you are using datetime data type, then you can not store only the time portion, at least in SQL Server 2005 or older versions.

You should be doing all formatting in your application and not in SQL Server as this is a presentation issue.

You could choose to use varchar data type instead.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-09-25 : 15:28:52

SELECT CONVERT(varchar(8),CreationTime,108)
FROM Table1

will return what you want to the front end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-25 : 15:29:31

But you still shouldn't do it in SQL Server regardless if there is a way to do it there.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

notes4we
Yak Posting Veteran

90 Posts

Posted - 2008-09-25 : 16:19:14

Thank you Jimf. It works.

notes4we
Yak Posting Veteran

90 Posts

Posted - 2008-09-25 : 16:22:40

Tkizer,

I have Excel as my source and SQL as my destination. I am using SSIS. I just wanted to use this for the views that I will be creating. And it is working fine. I do not understand why did you want me to avoid using it.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 01:11:58

quote:
Originally posted by notes4we

Tkizer,

I have Excel as my source and SQL as my destination. I am using SSIS. I just wanted to use this for the views that I will be creating. And it is working fine. I do not understand why did you want me to avoid using it.

Thanks.

Because SQL Server is a db engine and formating is not a purpose of it. You should be trying to do format stuffs in your front end application as long as you can do it there.Even if you store it as datetime datatype with both date and time part you can easily do these formatting at you front end. And since the value remains as datetime in your db (table/view) you can use them for date manipulations also easily and make use of date functions also for it. By making it varchar using CONVERT you're losing out on these advantages, just for sake of getting value in your required format.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources