Extracting the time from a 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

SQL Server 2005 Forums

Transact-SQL (2005)

Extracting the time from a datetime field

Author

Topic

theboyholty
Posting Yak Master

226 Posts

Posted - 2008-12-19 : 11:56:28

I've got two fields - DateCreated (e.g. 2008-12-19 00:00:00.000) and TimeCreated (e.g. 1900-01-01 16:07:35.000).
Where the 1900-01-01 is a set dummy date - its just the time that's accurate.
I want to show items recieved in the last 10 minutes but if I use datediff(minute,timecreated,getdate()), I get as a result the number of minutes since 1900 i.e. about 57 million.
How can I strip the time out so I can compare it with the time now and show the correct records?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-19 : 12:27:47

You can do it via the CONVERT function and the appropriate style, however you really should combine the two columns so that you can easily use date/time functions. You will see better performance too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-19 : 22:40:22

select right(convert(varchar(25),getdate(),0),7)
select right(convert(varchar(25),getdate(),100),7)

try this theboyholty
replace getdate() with ur datecolumn

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-19 : 22:42:43

SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-19 : 23:13:46

once go through this site u will get the date formats
http://www.sql-server-helper.com/tips/date-formats.aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-12-19 : 23:39:03

As Tara stated, you should store the value in a single column. Having it the way you do makes no more logical sense than storing the price of something in a whole dollars column plus a cents column.

A serious drawback of the split storage of the date is that it makes indexes on the column much less useful, and very difficult to write a query that can use them.

With that understanding of the flawed table design aside and dealing with the table you have in place, the query below will return correct results. Note that this query will not be able to make use of indexes on DateCreated and TimeCreated.

declare @t table( DateCreated datetime ,TimeCreated datetime)

-- Load Test Data
insert into @t
select '2008-12-19 00:00:00.000',  '1900-01-01 23:05:35.000' union all
select '2008-12-19 00:00:00.000',  '1900-01-01 23:08:35.000'


select
	*, getdate() as CurrDateTime
from
	@t
where
	DateCreated+TimeCreated > dateadd(minute,-10,getdate())
	
	
Results:
DateCreated             TimeCreated             CurrDateTime
----------------------- ----------------------- -----------------------
2008-12-19 00:00:00.000 1900-01-01 23:08:35.000 2008-12-19 23:18:34.573

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources