How can Insert or Select Only TIME from Table's column (06:23:23 AM/PM) format

Question

0.00/5 (No votes)

See more:

I am using MaskedEditExtender AjaxTools Control for time in my application how can i save only time-- in this format 11:23:22 AM/PM i have two column DataType is define as DateTime one is INTime (09:30:12 AM) and second one is OUTTime(06:30:12 PM) how can i write Insert query in Store Procedure.. HELP
I am starter with sql sevr and .net
i used DataType DateTime .....I think i use Time7 instead of datetime

Posted 25-Jul-13 19:05pm

Faizymca

Add a Solution

Comments

Jignesh Khant 26-Jul-13 1:16am

You can use 'DateTime.Now.ToShortTimeString().ToString()'.

Maciej Los 26-Jul-13 3:11am

What version of MS SQL Server?
Have you seen this: Data types (T-SQL)[^]

2 solutions

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)

Raja Sekhar S · Accepted Answer · 2013-07-25T20:13:00

Solution 1

Try This

SQL

Select Right(CONVERT(VARCHAR(25), GETDATE(), 22),11)
--Try above code for variable, Below one for Table..
Select Right(CONVERT(VARCHAR(25), ColumnName, 22),11) from TableNamee

Assuming that your column data type is DateTime and Version of Ms SqlSever is 2005 or above versions...
Hope this helps

Posted 25-Jul-13 20:13pm

Raja Sekhar S

Updated 25-Jul-13 21:11pm

v3

Comments

Maciej Los 26-Jul-13 2:41am

Raja, please, improve your answer. You need to remember that there are few verions of MS SQL Server and their data types[^] are differ.

Raja Sekhar S 26-Jul-13 3:12am

Maciej Los: Thanks for the Suggestion.. Updated the solution....

Maciej Los 26-Jul-13 3:23am

This is not exactly what i wanted to see ;(
Please, see my answer.
A 4!

Raja Sekhar S 26-Jul-13 3:47am

Thank you...

Maciej Los · Accepted Answer · 2013-07-25T21:18:00

Solution 3

Please, read my comment to the question. We can't help you to write SP having not enough information.

Solution 1 by Raja Sekhar S is very good, but i would suggest to change data type of field to time (T-SQL)[^] if your MS SQL Server is 2008 or later.

Another way to return only time part of date is to use below query (without RIGHT function):

SQL

SELECT CONVERT(VARCHAR(25), GETDATE(), 114) AS CurrentTime

If you would like to insert time value into table, you shouldn't convert it into varchar data type. Save as it is. To view it, you can always use CAST and CONVERT (T-SQL) functions[^].

Posted 25-Jul-13 21:18pm

Maciej Los

Updated 25-Jul-13 22:47pm

v4

Comments

Raja Sekhar S 26-Jul-13 3:45am

But op wanted the time along with AM/PM... That's why i used the Right Function.. Other wise i would have suggested Time.....

Maciej Los 26-Jul-13 4:39am

OK, i understand it now ;)

Faizymca 26-Jul-13 5:08am

Thnx Raja Sekhar S, I meet to my requirement of time format but here my out this form
9:30:00 AM but i want (0) if value is 09:30:00 AM and i used Sql Server 2008 so can you suggest me which datatype i used in TIME7 or Date Time...

Maciej Los 26-Jul-13 5:21am

Please, follow the link (posted in comment to the Raja's solution) and choose the best time format for you ;)
By The Way: Thank you for accepting my answer as solution ;)