Case Statment with Date/Time conversion

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 2008 Forums

Transact-SQL (2008)

Case Statment with Date/Time conversion

Author

Topic

SQLMAKESMECRY
Starting Member

14 Posts

Posted - 2011-03-16 : 08:45:54

My date/time format is:
ENT_DATE
2009-06-10 13:33:14.000

Here is my convert line
SELECT convert(varchar, ent_date, 114)
from ae_p_pro_e

That returns this:
(No column name)
13:33:14:000

I am trying to sort the time to use in a Case Statement:
SELECT
CASE ent_date
WHEN between 06:00:00.000 and 18:00:00.000 THEN 'AM'
ELSE 'PM'
END AS time_class
FROM ae_p_pro_e

How do I structure the nested select in the case statement?

Help please!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-16 : 08:54:04

What does the query return?

Madhivanan

Failing to plan is Planning to fail

SQLMAKESMECRY
Starting Member

14 Posts

Posted - 2011-03-16 : 09:05:33

It returns nothing. The query is wrong.

I started like this

SELECT
CASE ent_date
WHEN '2009-06-10 13:33:14.000' THEN 'pm'

ELSE 'Unknown'
END
FROM ae_p_pro_e

returning this:
(No column name)
pm
Unknown
Unknown

Realizing I could not think of a way to classify thousand of variable dates into two categories AM or PM I started attempting to use a case statement with the date/time conversion

Cricket

It's not fun unless I'm pulling my hair out. Weee!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-16 : 10:04:08

You have not specified the condition for AM

Madhivanan

Failing to plan is Planning to fail

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-03-16 : 10:18:58

Maybe:


CASE 
	WHEN DATEADD(d, -DATEDIFF(d, 0, ent_date), ent_date) BETWEEN '19000101 06:00:00' AND '19000101 18:00:00'
	THEN 'AM'
	ELSE 'PM'
END AS time_class

SQLMAKESMECRY
Starting Member

14 Posts

Posted - 2011-03-16 : 10:19:23

I like to minimize the data as much as possible when trying to figure out the sql.
I only care about defining the hours between 6:00am and 6:00pm. Which are in 'yyyy dd mon hh:mm:ss:mmm'
The collected rows could return 'workday','pm','am'.. etc. it doesn't matter.

I am trying to create a data set to define a parameter in BIRT.
I need to allow my users to see only the work orders created between 6am and 6pm.
The other times can be defined as 'not_important' :)

Cricket

It's not fun unless I'm pulling my hair out. Weee!

SQLMAKESMECRY
Starting Member

14 Posts

Posted - 2011-03-16 : 10:19:50

quote:

Originally posted by Ifor

Maybe:


CASE 
	WHEN DATEADD(d, -DATEDIFF(d, 0, ent_date), ent_date) BETWEEN '19000101 06:00:00' AND '19000101 18:00:00'
	THEN 'AM'
	ELSE 'PM'
END AS time_class

oo let me try

Cricket

It's not fun unless I'm pulling my hair out. Weee!

SQLMAKESMECRY
Starting Member

14 Posts

Posted - 2011-03-16 : 10:21:41

quote:
Originally posted by SQLMAKESMECRY

quote:
Originally posted by Ifor

Maybe:

CASE
WHEN DATEADD(d, -DATEDIFF(d, 0, ent_date), ent_date) BETWEEN '19000101 06:00:00' AND '19000101 18:00:00'
THEN 'AM'
ELSE 'PM'
END AS time_class

GENIUS -- I would HUG you if I could!!
oo let me try

Cricket

It's not fun unless I'm pulling my hair out. Weee!

Cricket

It's not fun unless I'm pulling my hair out. Weee!

SQLMAKESMECRY
Starting Member

14 Posts

Posted - 2011-03-16 : 10:25:24

Thank you so much IFor and I appreciate your effort madhivanan. I don't know why I do that.. I get stuck on trying one method and forget to get out of my box!

Cricket

It's not fun unless I'm pulling my hair out. Weee!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources