get time value from date/time field

Question

using vs2008 and programming in vb

i have a form that builds a datagridview from a sql statement and i need to format one column to show only the time from the datetime column

in my query, i have 2 tables inner joined so my statement looks like

select [table name].[column name] as Time inner join....

how do i get only the time from that column still using the alias and keeping the tables inner joined?

Answer 1

You can modify you sql statement as follows

select convert(varchar(10), Tablename.columname, 108) AS Time

from Tablename

Inner Join ...

---

Thanks, A.m.a.L [MVP Visual C#] Dot Net Goodies
Don't hate the hacker, hate the code

Answer 2

i tried your suggestion, but the column still comes up as date/time

Answer 3

Have you executed the query in SQL?

Can you post the complete query here.

---

Thanks, A.m.a.L [MVP Visual C#] Dot Net Goodies
Don't hate the hacker, hate the code

Answer 4

"SELECT [tech sheet].[date] as [Date], convert(varchar(10),[tech sheet].[time], 108) as [Time], [tech sheet].[pr number] as [PR Number], [models].[models] as Model, [tech sheet].[billing code] as [Billing Code],[tech sheet].[comments] as Comments FROM models INNER JOIN `tech sheet` ON ([models].[model number])=([tech sheet].[model number]) where ([models].[models]) like '"

 

& model.Text & "%' AND date >= #" & Date7.Value.Date & "# AND date <= #" & Date8.Value.Date & "# AND ([tech sheet].[billing code]) LIKE 'X%'"

 

Answer 5

What type of column is [tech sheet].[time] ?

---

Thanks, A.m.a.L [MVP Visual C#] Dot Net Goodies
Don't hate the hacker, hate the code

Answer 6

the time column contains both date and time - from the form - the code from that form's is to fill the column based on the current system time. the value taken from the form for that column is  timeofday. it was meant to only pull the time, but it grabs the date as well, so i need to only take the time from that field.

Answer 7

in db change the column type of timesheet.time to datetime and the query will work.

---

Thanks, A.m.a.L [MVP Visual C#] Dot Net Goodies
Don't hate the hacker, hate the code

Answer 8

in db change the column type of timesheet.time to datetime and the query will work.

---

Thanks, A.m.a.L [MVP Visual C#] Dot Net Goodies
Don't hate the hacker, hate the code

Answer 9

the colum is already formatted as date/time.

in the database the time is the only thing displayed.

Answer 10

using vs2008 and programming in vb

You did not tell which brand car you drive?

I don't see anything related to Visual Basic in your question.

It is completely Transact SQL

Would you not be better of to ask this in a SQL transact forum,

Although A.M.A.L like me knows probably the answer on this question are other persons searching for VB solutions and can be mislead by your question.

 

 

---

Success
Cor

Answer 11

try this

select Convert(varchar,getdate(),8)

replace getdate() with your column name.

---

Thanks, A.m.a.L [MVP Visual C#] Dot Net Goodies
Don't hate the hacker, hate the code

Answer 12

sorry i didn't post this in a sql forum - i chose vb because i am using the sql query inside of a vb form.

when trying select Convert(varchar,[tech sheet].[time],8)  as time

i get Undefined function 'convert' in expression.

Answer 13

I am not getting any error. Check this

---

Thanks, A.m.a.L [MVP Visual C#] Dot Net Goodies
Don't hate the hacker, hate the code

Answer 14

sorry i didn't post this in a sql forum - i chose vb because i am using the sql query inside of a vb form.

when trying select Convert(varchar,[tech sheet].[time],,8)  as time

i get Undefined function 'convert' in expression.

I was curious if you did that in your car, therefore I asked what car you was driving?

Maybe you can ask this question also in the forum of that brand.

---

Success
Cor

Answer 15

still no luck -

i tried just copying the data from the datagridview and pasting it into excel and then use excel to format the column by time, and it wouldn't format it that way either, so i think the code is correct from your first suggestion, but given it won't format in excel, i'm lead to believe it's something with the db.

Answer 16

---

in this example you say to replace getdate () with the name of my column so it would be time - but it is from the tech sheet table not the models table - so when i said convert(varchar, [tech sheet].[time](), 8) AS Time-  it gave me the error.  am i doing this incorrectly?

Answer 17

Hello,

You don't need the () after [time]

That's a syntax error.

Adam

---

Dibble and dabble but please don't babble.

Answer 18

it gives me the same error with or without the ()

Answer 19

it gives me the same error with or without the ()

 select Convert(varchar,[tech sheet].[time],,8)  as time

You have added 2 comma's, remove one

 

---

Thanks, A.m.a.L [MVP Visual C#] Dot Net Goodies
Don't hate the hacker, hate the code

Answer 20

Try this:

CONVERT(CHAR(8), [Time Sheet].[Time],8)

Adam

---

Dibble and dabble but please don't babble.

Answer 21

that was an error on my part when i typed it here - my code only has one

CONVERT(CHAR(8), [Time Sheet].[Time],8)

didn't work either...i tried to reformat the data in the db, but it does the same thing, however, i did notice that the date isn't the date the data was entered, it is always 12/30/1899

Answer 22

that was an error on my part when i typed it here - my code only has one

CONVERT(CHAR(8), [Time Sheet].[Time],8)

didn't work either...i tried to reformat the data in the db, but it does the same thing, however, i did notice that the date isn't the date the data was entered, it is always 12/30/1899

In the form you are passing only the time. Instead try to pass the complete date object. Let the sp get back only the time field.

For testing can you use Convert(varchar(10), getDate(), 8) as Time

and see whether only the time is sending back to the UI. So we can nail down the issue to "improper data in column(12/30/1899)"

---

Thanks, A.m.a.L [MVP Visual C#] Dot Net Goodies
Don't hate the hacker, hate the code

Answer 23

Hello,

What datatype is the field in the table?

Adam

---

Dibble and dabble but please don't babble.

Answer 24

SELECT timeonly = CONVERT(CHAR(8),[Time Sheet],8) FROM  models

Note that Time is a reserved word and that it is preferrable not to allow spaces while naming your DB objects.

 

 

---

Only performance counts!

Answer 25

i'll try this asap and let you know what i get

Answer 26

i'll try this too - thanks...

i didn't create the database...just querying it and adding new information to it...if i had, i wouldn't have had spaces or used any of the reserved keywords... thanks for the input though ;-)

Answer 27

For testing can you use Convert(varchar(10), getDate(), 8) as Time

when i use this, i get a reserved word error.

Answer 28

that error was due to not putting as time in [] like as [time]

but after doing that i get an error about convert

Answer 29

that error was due to not putting as time in [] like as [time]

but after doing that i get an error about convert

I gave you a code that works perfectly. And how come that you abandoned this thread since? Please run this code:

SELECT timeonly = CONVERT(CHAR(8),[Time Sheet],8) FROM  models

---

Only performance counts!

Answer 30

i have been away and haven't been able to get back to the thread...

i tried the code without any success....

i am going to try to reformat the database with different column names and change how the form which writes to the database records the date.  the problem has to lie within the database, not the sql you are providing me. 

i thank all of you for the help.

Answer 31

i tried the code without any success....

This code works perfectly: SELECT timeonly = CONVERT(CHAR(8),[Time Sheet],8) FROM  models

---

Only performance counts!

Answer 32

This code works perfectly: SELECT timeonly = CONVERT(CHAR(8),[Time Sheet],8) FROM  models

as i said before...

when trying this code i get an error.

undefined function 'convert' in expression

 

Answer 33

Below is the result from my SQL Management Studio. Perhaps you did not apply it properly in code. Can you post your entire code if it is not too long and complicated(Note: I don't enjoy helping with long codes but other forum users may assist you):

 

---

Only performance counts!