getting time portion - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

getting time portion

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-01-24 : 21:54:22

Hi friends
i have a datetime field and i want to write a query which gets only time portion .
if time is <12 then it should be 00:00 am (e.g. say 10.10 am) if after 12 then 00:00 pm (e.g. say 1.30pm)
how can i do that please
Thanks

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-24 : 22:01:26

use convert(). Refer to Books OnLine for detail

----------------------------------
'KH'
I do work from home but I don't do homework

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-01-24 : 22:13:12

i looked at it and i tried like this
select CONVERT(varchar(max),GETDATE() , 109)
and getting result back is
Jan 25 2006 4:11:51:183PM

so does it mean that i need to parse abv string to get what i want. i thought there would be a built in function for that.
Thanks

Cheers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-24 : 22:17:55

use substring() on the result of convert() to get only the time portion that you required

select substring(CONVERT(varchar(26),GETDATE() , 109), 13, 14)

----------------------------------
'KH'
I do work from home but I don't do homework

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-24 : 22:18:30

Or use your Font End application to format the time

----------------------------------
'KH'
I do work from home but I don't do homework

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-01-24 : 22:24:34

Thanks for that i get it :)

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-25 : 01:39:12

Where do you want to show the time?
If you use Front End application, dont use convert function in query. Format it at Client Application.

Madhivanan

Failing to plan is Planning to fail

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-25 : 06:17:59

Why not just use convert(varchar,getdate(),108)??

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-01-25 : 14:39:26

Madhivanan
i needed it for a view. same view being used in a .net app and in reporting services. so we want to do that in one location.

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-01-25 : 14:41:13

Hi RickD
thats what i finally ended up doing. actually i needed AM and PM also. which above Convert did not provide so i added some custom code to include AM and PM also.

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources