Date conversion into AM/PM Format

Question

Date conversion into AM/PM Format

anitha.cherukuri

SSCommitted

Points: 1514
More actions
November 3, 2009 at 3:18 pm

#227694

Hi I have time in my table like this
Time
16:20
i want to see like
time
4:20 pm
can anybody help me in this issue,thanks.
ani

Viewing 14 posts - 1 through 13 (of 13 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Actually, not quite enough information. How is the data actually stored in the table, is the column defined as a datetime data type?

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 2

hi

Data type is decimal ,its time field and extracted from AS 400 systems.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Okay, it is a decimal value, how is it stored? Please provide a sample of the data.

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 4

it was stored in below format

14,23

15,59

12,12

13,45

what i wwant here

14,23 =2:23 PM

15,59=3:59 PM

tahnks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

anitha.cherukuri (11/3/2009)
it was stored in below format
14,23
15,59
12,12
13,45
what i wwant here
14,23 =2:23 PM
15,59=3:59 PM
tahnks

Being in the US, I am assuming the comma (,) is just like the decimal point (.) here, correct?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Here is the code I developed.

declare @time decimal(4,2);

set @time = 14.23;

select right(convert(varchar(20), dateadd(mi, (@time - floor(@time)) * 100, dateadd(hh, floor(@time), 0)), 100), 7)

Barney.Qian SSC Journeyman Points: 93 More actions · Answer 7

select right(convert(varchar(18),getdate(),100),7)

Is that right?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

q123126 (11/3/2009)
select right(convert(varchar(18),getdate(),100),7)
Is that right?

Works on my computer at home.

Edit: Except you need to change the varchar(18) to varchar(19) or varchar(20) as I originally posted. You could also add an ltrim to the whole thing to eliminate the leading space for single digit hours.

anitha.cherukuri SSCommitted Points: 1514 More actions · Answer 9

Hi

There is no comma in between 13,40.

I typed for better understanding.

actual format is 1340 ,1220,1440 and data type is decimal.

anyway thanks for help,

it gives me idea....

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

One now begs to ask... is it DECIMAL or INT? 😛

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Lynn Pettis

SSC Guru

Points: 442467

November 8, 2009 at 9:42 pm

#1076517

And if it is decimal, how is it declared?

suresh.theyyath Old Hand Points: 304 More actions · Answer 12

Hope this will works for you!

http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

suresh.theyyath (11/10/2009)

Hope this will works for you!

http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/%5B/quote%5D

Heh... I wrote such a function earlier in my SQL career... it's a fun function to write because it creates a huge understanding of DATETIME datatypes, but don't expect any real performance out of it. It tends to be quite slow especially compared to straight-ahead inline code.

--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)