SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Extract time from datetime field
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

tmcheah
Starting Member

Malaysia
2 Posts

Posted - 08/23/2004 :  03:49:36  Show Profile
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

Slovenia
11749 Posts

Posted - 08/23/2004 :  04:11:05  Show Profile  Visit spirit1's Homepage
select convert(varchar(10), sysdate, 108)

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

Edited by - spirit1 on 08/23/2004 04:11:13
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/23/2004 :  04:13:59  Show Profile
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
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 08/23/2004 :  08:12:44  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/23/2004 :  14:16:12  Show Profile
'Coz I didn't read it! But I've noted it in my Snippets file ...

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 08/24/2004 :  03:39:41  Show Profile  Visit spirit1's Homepage
snippets file? is there a way to see that file? might be interesting...

Go with the flow & have fun! Else fight the flow :)
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/24/2004 :  12:40:35  Show Profile
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 08/24/2004 :  12:56:18  Show Profile  Visit spirit1's Homepage
no wonder they gave you a nick "Test" :)))))

Go with the flow & have fun! Else fight the flow :)
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/24/2004 :  13:25:26  Show Profile
Yeah, some people have all the fun, eh?!!

Kristen
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 08/24/2004 :  21:21:53  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000