Comparing 12-Hr Dates - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Comparing 12-Hr Dates

Author

Topic

reagent
Starting Member

4 Posts

Posted - 2008-04-04 : 15:01:31

I will try to explain my problem as best as possible without going into too much detail.

Basically I have two tables,
AgentSchedule
AgentPunchData

These tables both contain agent names.

The schedule table has a column called "SHEND" which is a DATETIME datatype which identifies when an agent shift should end.

The entries all have a date attached which means nothing, for example "12/30/1899 9:00:00 PM"

The PunchData table has a column called "LOGOUT" which is also a DATETIME, the entries will have the real date and look something like this: "3/28/2008 9:23:00 PM"

I need to strip the date out of these functions so I can compare the times. I need to query the databases and return any instances where the LOGOUT time is > the shift end time (SHEND).

So far nothing I've tried has been successful. I'm completely stumped, I've tried so many different things and nothing compares the two times correctly. SQL DATETIME is the biggest pain in the ass, I wish we had SQL 2008 so I could just use TIME.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-04 : 15:11:19

use CONVERT() with style 108 function to return only time part. look into BOl for CAST & CONVERT to get syntax

reagent
Starting Member

4 Posts

Posted - 2008-04-04 : 16:01:36

That was the first thing I tried. It doesn't work.

Actually I made a mistake, the entries actually look like this "12/30/1899 21:00:00.0000"

I have use this:

datepart(hh, dbo.AgentSchedule.SHEnd)*60+datepart(mi, dbo.AgentSchedule.SHEnd) as 'ShiftEnd',
datepart(hh, dbo.AgentTime4.LogoutDateTime)*60+datepart(mi, dbo.AgentTime4.LogoutDateTime) as 'Punchout'

to create two new columns to compare.

One of the entries in the table comes out like this:

Shiftend 1260
Punchout 1290

Now if I put this into the SQL query:

WHERE 'Punchout' < 'Shiftend'

it still returns the entry above. What gives? 1290 is clearly greater than 1260.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-04 : 16:10:48

The code below converts a SQL Server datetime value to a time only datetime value with the time as an offset from 1900-01-01 00:00:00.000.

By convention, time only is usually stored this way in SQL Server. The reason for this is that the time can be added to a datetime value containing the date only, and produce the original date and time.

Time Only Function: F_TIME_FROM_DATETIME:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65358



select
	[DateTime]	= a.DT,
	[Time Only]	= a.DT-dateadd(dd,datediff(dd,0,a.DT),0)
from
	(
	-- Test data
	select DT = convert(datetime,'2006-04-27 07:23:11.247')
	union all
	select DT = convert(datetime,'2006-04-27 07:21:09.333')
	union all
	select DT = convert(datetime,'1753-01-01 00:00:00.003')
	union all
	select DT = convert(datetime,'1753-01-01 07:21:09.997')
	union all
	select DT = convert(datetime,'1753-01-01 23:59:59.997')
	union all
	select DT = convert(datetime,'9999-12-31 00:00:00.003')
	union all
	select DT = convert(datetime,'9999-12-31 07:21:09.997')
	union all
	select DT = convert(datetime,'9999-12-31 23:59:59.997')
	) a


Results:
DateTime                                               Time Only
------------------------------------------------------ ------------------------
2006-04-27 07:23:11.247                                1900-01-01 07:23:11.247
2006-04-27 07:21:09.333                                1900-01-01 07:21:09.333
1753-01-01 00:00:00.003                                1900-01-01 00:00:00.003
1753-01-01 07:21:09.997                                1900-01-01 07:21:09.997
1753-01-01 23:59:59.997                                1900-01-01 23:59:59.997
9999-12-31 00:00:00.003                                1900-01-01 00:00:00.003
9999-12-31 07:21:09.997                                1900-01-01 07:21:09.997
9999-12-31 23:59:59.997                                1900-01-01 23:59:59.997

(8 row(s) affected)

CODO ERGO SUM

reagent
Starting Member

4 Posts

Posted - 2008-04-04 : 16:27:20

I just want to know what could POSSIBLY be causing SQL to return the row:

Shift End Punchout
1260 1290

When I have specified "WHERE 'Punchout' < 'ShiftEnd'"

I used Datepart to create those columns so they should be INTs.

reagent
Starting Member

4 Posts

Posted - 2008-04-04 : 16:32:20

The DATEPART function in SQL is clearly defined on the MSDN website as being used to "Return an integer representing the specified datepart of the specified date."

However, when I use it to create my two new columns they obviously aren't INTs or they would compare correctly using boolean.

I confirmed this suspicion by trying this:

cast('Punchout' as int)

SQL throws the error "Conversion failed when converting varchar value 'Punchout' to data type int.

This is extremely frustrating. I can't believe such a simple task could be so absurdly difficult in SQL

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-04 : 16:37:37

'punchout' is a string literal, not a column name. Don't delimit column or table names with apostrophes. If you need to delimit a column name for some reason, use [ and ] .

i.e.,

select x from table

return the value of the column x from the table, but:

select 'x' from table

just returns the string literal 'x' .

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources