How to extract time portion of datetime along with AM or PM?

Question

Post reply

How to extract time portion of datetime along with AM or PM?

Teja-444197

Mr or Mrs. 500

Points: 514
More actions
June 11, 2007 at 6:22 pm

#117496

Hello,
I am storing a time value into database using a DateTime datatype where the date portion is defaulted to 1/1/1900 e.g., 1/1/1900 4:23:14 PM. I would like to display ONLY time portion of this datetime. I tried to use CONVERT function on datetime like this: CONVERT(nvarchar, TimeRecording, 108), but this returns only hh:mm:ss without AM or PM suffix. Please suggest if there is another way to obtain time as hh:mm:ss AM/PM. Thanks a lot!
Teja

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110209 More actions · Answer 1

Open BOL on "CAST and CONVERT" and choose one of formats having AM/PM.

108 is not the only one.

_____________
Code for TallyGenerator

Teja-444197 Mr or Mrs. 500 Points: 514 More actions · Answer 2

hello, I referred BOL and found only two styles that emit datetime in hh:mm:ss format. They are style 108 and style 114. Neither of them return AM or PM.

Sergiy SSC Guru Points: 110209 More actions · Answer 3

Actually 109, for example, brings you time portion.

If you don't want to see date in the string just apply some string manipulations to remove unwanted characters.

One of the options:

SELECT STUFF(SUBSTRING(CONVERT(nvarchar(30), GETDATE(), 109), 13, 14), 9, 4, '/')

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110209 More actions · Answer 4

BTW,

you better don't separate date and time in tables.

It's just wasting space, time for coding and losing performance of queries.

_____________
Code for TallyGenerator

David McFarland SSChampion Points: 11815 More actions · Answer 5

I'm with Sergiy in that I wouldn't recommend actually storing the time in this manner (displaying it, on the other hand, is peachy). It makes date arithmetic easier, but also acts as sort of a built in constraint. While an nchar time column would happily accept 30:94:86 as hh:mm:ss, a datetime column will keep that from happening.

Teja-444197 Mr or Mrs. 500 Points: 514 More actions · Answer 6

Thank you. Let me clarify what you are suggesting. I am implementing a ASP.NET form with a date field DateRecorded and a time field TimeRecorded. Do you suggest that I store these values in a single DateTime column and use the date portion of it for DateRecorded and time portion for TimeRecorded, instead of using two DateTime columns?

David McFarland SSChampion Points: 11815 More actions · Answer 7

You can store them together or separately depending on your needs, but what we're saying is that whether together or separate, use datetime columns to do the storing. From the description you give, I'd probably store them together, even though you are getting them from two different columns, but again, that's up to you and doesn't impact the recommendation of data type to use.

Sergiy SSC Guru Points: 110209 More actions · Answer 8

Try to cut the code to calculate duration of some overnight event - started on one day and finished on another.

Compare it with simple DATEDIFF.

And don't put yourself in trouble.

It's just simple example. Not the only one.

Separate columns for date and time is a permanent headache.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 9

Simply stated... YES... that's exactly the way to do it. As Serqiy pointed out, there are some performance issues that will resolve AND there are other benefits down the line, as well.

There are some folks that will also tell you that the apparent "splitting of date/time" should always be accomplished in the GUI... not in SQL... for the most part, I agree with that. Sure, you can do some awesome formatting in SQL but formatting is what the GUI is for, anyway. Keep the "Presentation Layer" stuff in the "Presentation Layer" unless you don't have a GUI as in some form of batch processing to produce text files for 3td parties.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Teja-444197 Mr or Mrs. 500 Points: 514 More actions · Answer 10

Thanks a lot for your suggestions! I really appreciate it.