• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

how can I get only the time from a datetime column that stores a bunch of datetimes

I have a column in a table which stores dates and times how can I query that table so that I get only the time portion
on the web I only found ways to get the current time with
CONVERT(VARCHAR,getdate(),108)
but that is not what I want I want to query a specific column
vb.net 2008 express
0
Dov_B
Asked:
Dov_B
  • 6
  • 5
  • 4
  • +3
4 Solutions
 
Meir RivkinFull stack Software EngineerCommented:
taken from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56079

If it is only for Disply purpose then
select Convert(varchar,getdate(),8)
Otherwise use Format function in your Front End Application

If you have to show the the milliseconds:
select [time]=right(convert(varchar(30),getdate(),121),12)
0
 
Reza RadCommented:
you can do like this:
select datetimefield,convert(varchar(max),datetimefield,108) as timeonly
from yourtable
0
 
liorfrCommented:
Lets say you have a users table with a datetime column named ModifiedDate.
The following will get the time portion

select CONVERT(VARCHAR,ModifiedDate,108) from dbo.Users
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Meir RivkinFull stack Software EngineerCommented:
if in frontend u wish to get the time part:

dim dt  as DateTime = DateTime..Parse(<select query result>);
dim lStime as string = dt.ToShortTimeString()

or

dim lStime as string = dt.TimeOfDay.ToString()
0
 
Dov_BAuthor Commented:
thanks all of you actualy liorfr explained it in a way I was able to understand
one problem though it returns military time like 17:00
how do I get it to return 2:00 pm
0
 
Meir RivkinFull stack Software EngineerCommented:
@liorfr

very nice...
0
 
Reza RadCommented:
select right(CONVERT(varchar(max),modifiedDate,109),14) from dbo.Users
0
 
Dov_BAuthor Commented:
dear reza rad I am using vb.net express with sqlce it doesnt seem to recognize your code
0
 
Reza RadCommented:
what error do you receive?
0
 
Dov_BAuthor Commented:
sorry took so long to get back
error parsing query token in query = RIGHT
0
 
Reza RadCommented:
ok,so try this:

select SUBSTRING(CONVERT(VARCHAR,modifieddate,109) ,13,LEN(CONVERT(VARCHAR,modifieddate,109) )-14) as f1 from dbo.users

Open in new window

0
 
Dov_BAuthor Commented:
ok it got rid of the military time but it gave me
1:00:05:000
I dont like those three zeros at the end
is it possible get rid of them?
also is it possible to get am and pm?
0
 
sameer2010Commented:
Use this:
SELECT right('0'+ ltrim(right(CONVERT( varchar, getDate(), 100),7)),8)

Open in new window

0
 
Dov_BAuthor Commented:
i am using sqlce it does not seem to support right
0
 
sameer2010Commented:
select substring('0' +ltrim(right(CONVERT( varchar, getDate(), 100),7)),-6+len(ltrim(right(CONVERT( varchar, getDate(), 100),7))),8)
0
 
sameer2010Commented:
Ignore this....got posted by mistake
0
 
ralmadaCommented:
If you just want hours and minutes:

SELECT substring(CONVERT( varchar, yourfield, 100), 13, 7) from yourtable
if you want hours, minutes and seconds

SELECT stuff(substring(convert(varchar, yourfield, 109),13,100),9, 4, '') from yourtable
0
 
sameer2010Commented:
I was trying to 0 padd...but it would too lengthy to do that if RIGHT is not supported
0
 
Reza RadCommented:
try this :

select SUBSTRING(CONVERT(VARCHAR,modifieddate,109) ,13,LEN(CONVERT(VARCHAR,modifieddate,109) )-18)+' '+SUBSTRING(CONVERT(VARCHAR,modifieddate,109) ,25,LEN(CONVERT(VARCHAR,modifieddate,109) )-24) as f1 from dbo.users

Open in new window

0
 
ralmadaCommented:
Have you tried my suggestion http:#a26181454 before closing the question?
0
 
Dov_BAuthor Commented:
oh boy I just tried it now and it is much better than the other answers I am so sorry can I post a new question and have you answer it so I can award you the points?
0
 
ralmadaCommented:
No need for that. Click on the "Request Attention" link and a moderator will re-open the question so you can reassign points.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 6
  • 5
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now