Extract time from datetime field

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

Old Forums

CLOSED - General SQL Server

Extract time from datetime field

Author

Topic

tmcheah
Starting Member

2 Posts

Posted - 2004-08-23 : 03:49:36

How do I extract the time from the datetime field? Let say the sysdate field contain value '2003-01-19 12:30:01'. It is a datetime field. How do I just extract the time '12:30:01' from the sysdate field?

Please help. Thanks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-23 : 04:11:05

select convert(varchar(10), sysdate, 108)

Go with the flow & have fun! Else fight the flow :)

Kristen
Test

22859 Posts

Posted - 2004-08-23 : 04:13:59

Either by converting it to a varchar, with appropriate format, and using SUBSTRING to get the relevant bit or, if you want the result kept as a datetime datatype, subtracting the "date".

So I guess that would be something like:


SELECT SUBSTRING(CONVERT(varchar(24), GetDate(), 121), 12, 12)
and
SELECT DATEADD(Day, 0-DATEDIFF(Day, 0, GetDate()), GetDate())

respectively (Second has the Date as 01-Jan-1900 which seems to be the generic way to store just a time in SQL Server!)

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-23 : 08:12:44

Why would you do that when you can just use the example spirit gave Kristen?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-08-23 : 14:16:12

'Coz I didn't read it! But I've noted it in my Snippets file ...

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-24 : 03:39:41

snippets file? is there a way to see that file? might be interesting...

Go with the flow & have fun! Else fight the flow :)

Kristen
Test

22859 Posts

Posted - 2004-08-24 : 12:40:35

I've got loads of snippets files!

Here's part of my Date & Time one


Seconds Test
-------  ----------------------------
  1.453 SELECT COUNT(*) FROM MyTable

(Result = 12,689,651 rows)

  6.813 SELECT @TempDate = MyDate FROM MyTable

  6.623 SELECT @TempDate = DATEADD(day, DATEDIFF(day, 0, MyDate), 0) FROM MyTable

 37.203 SELECT @TempDate = CONVERT(varchar(8), MyDate, 101) FROM MyTable

-- Causes overflow
-- SELECT 'Second', DATEADD(Second, DATEDIFF(Second, 0, GetDate()), 0)
-- UNION ALL

SELECT 'Minute', DATEADD(Minute, DATEDIFF(Minute, 0, GetDate()), 0)
UNION ALL
SELECT 'Hour', DATEADD(Hour, DATEDIFF(Hour, 0, GetDate()), 0)
UNION ALL
SELECT 'Day', DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)
UNION ALL
SELECT 'Week', DATEADD(Week, DATEDIFF(Week, 0, GetDate()), 0)
UNION ALL
SELECT 'Month', DATEADD(Month, DATEDIFF(Month, 0, GetDate()), 0)
UNION ALL
SELECT 'Quarter', DATEADD(Quarter, DATEDIFF(Quarter, 0, GetDate()), 0)
UNION ALL
SELECT 'Year', DATEADD(Year, DATEDIFF(Year, 0, GetDate()), 0)

-- Just time:
select convert(varchar(10), GetDate(), 108)

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-24 : 12:56:18

no wonder they gave you a nick "Test" :)))))

Go with the flow & have fun! Else fight the flow :)

Kristen
Test

22859 Posts

Posted - 2004-08-24 : 13:25:26

Yeah, some people have all the fun, eh?!!

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-24 : 21:21:53

I still like this one, which I post on here all the time.


SET NOCOUNT ON

DECLARE
	@min INT,
	@max INT,
	@date DATETIME

SELECT
	@min = 1,
	@max = 131,
	@date = GETDATE()

SELECT @date

WHILE @min <= @max
BEGIN

	IF @min BETWEEN 15 AND 19 
		OR @min  = 26 
		OR @min BETWEEN 27 AND 99
		OR @min BETWEEN 115 AND 119
		OR @min BETWEEN 122 AND 125
		OR @min BETWEEN 127 AND 129
		BEGIN
			GOTO NEXT_LOOP
		END

	SELECT @min, CONVERT(VARCHAR,@date,@min)

NEXT_LOOP:

SELECT @min = @min + 1
END

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources