Extracting time from datetime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Extracting time from datetime

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-19 : 05:49:35

Hi all

I've got a timestamp in the format yyyy-mm-dd HH:mm:ss.000 (it's 24hr time).
I can extract the hour (which is straightforward using datepart) but it gives me an integer.
I need to convert this integer to a "proper" time in the format HH:mm:ss.

Anyone any ideas as I can't seem to cast/convert it?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 05:52:08

convert(varchar(8),dte,108)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-19 : 06:01:32

Apologies, that was badly written, it might help if I give you an example.
If I have a datetime of 2010-11-19 11:15:00.000, I need an output of 11:00:00.

Hope that makes a bit more sense.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 06:04:45

sorry
convert(varchar(2),dte,108)+':00:00'
or
convert(varchar(8),deatedd(hh,datediff(hh,0,dte),0),108)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-19 : 06:20:09

Brilliant, thank you.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-19 : 07:36:20

As an add-on to the above, I need to run the query between two specific times.
I've got the start/end dates set up using getdate()-2 and getdate()-1 respectively.
and this is the line of code I'm trying to use:-


cast(ap.TimeStamp as varchar) between cast(@start_date as varchar) + '17:00:00.000' and cast(@end_date as varchar) + '16:59:59.000'

It's not working and I've tried removing the casts but then it just gives me an error about conversion from string to date/time.
anyone any ideas on this one?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 07:48:00

ap.timestamp between @start_date + '17:00:00' and @end_date + '16:59:59'

assuming that timestamp and the variables are all datetimes.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-19 : 08:14:01

I'd originally got the start/end dates set as dates not datetimes. That explains a lot.
Cheers.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-19 : 08:21:57

ap.timestamp >=@start_date + '17:00:00' and ap.timestamp< @end_date + '17:00:00'

Madhivanan

Failing to plan is Planning to fail

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-11-19 : 08:37:51

SELECT CONVERT(VARCHAR,GETDATE(),108).this will retreive only time from the date