How to get time form 24 hs to 12 hr in sql server ?

Question

5.00/5 (1 vote)

See more:

Hi
I have table,in that i declare one column name as DateSend as nvarchar(10) and i store time as
10.12.11
15.45.44
17.00.00

form above i need to convert to 12 hr like

10:12:11 AM
03:45:44 PM
05:00:00 PM

what is the sql query ?

Regards

Aravind

Posted 11-Sep-14 21:41pm

Aravindba

Add a Solution

Comments

Duncan Edwards Jones 12-Sep-14 4:01am

Why are you storing a date in an NVarchar column - why not use the appropriate data type?

Aravindba 12-Sep-14 5:04am

yes,actually this is existing one,in most of the place need to show like 15.33.13,so we declare as nvarchar.But in report i need to show 12hr format time like 03:33 or 03:33 PM

Thanks7872 12-Sep-14 4:45am

Use reply button while commenting to any member's comment.

Thanks7872 12-Sep-14 4:46am

If in most of the places you need some specific format,than you should handle it on code side.

Aravindba 12-Sep-14 5:04am

thank u for ur reply,in most of plac i dont want any format,just what i save in sql ,that only need to show like 15.33.13,in report one column i need to show as 12hr format.

Thanks7872 12-Sep-14 5:37am

You misunderstood what i stated. You stored in DB as 15.33.13 just because you need that format in most of the places,right? so just get data from db and display it.

My point is, it should not be that way. You should use proper datatype to store such content and at the time of displaying it, you can format it in whatever format you like such as 15.33.13 . It would be much easier for you to solve such issues like you are facing right now.

Aravindba 12-Sep-14 5:57am

i understand ur point,that date save form service,we have on service,that service save like that and it show in website report.now i am not have service code to change.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

george4986 · Accepted Answer · 2014-09-11T23:37:00

Solution 2

assuming ur table name as table1 and column as DateSend of datatype nvarchar(10)

try this

SQL

select REPLACE(CONVERT(varchar(20),CAST(REPLACE(DateSend ,'.',':') AS TIME),109),'.0000000',' ') FROM table1

good luck ;-)

Posted 11-Sep-14 23:37pm

george4986

Comments

Aravindba 12-Sep-14 5:59am

Thanx a lot,it work,thanx bro

george4986 12-Sep-14 6:08am

u are always welcome ;-)