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 :)
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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 :)
Go to Top of Page

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
Go to Top of Page

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 :)
Go to Top of Page

Kristen
Test

22859 Posts

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

Kristen
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -