DATEPART Calculation - 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

General SQL Server Forums

New to SQL Server Programming

DATEPART Calculation

Author

Topic

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 11:33:58

I have a column called TktOpenCoverageTime which needs to show the opening CoverageTime based on start time from another column. The problem is if the time is anywhere from 12am - 5am I want it to return 5am no matter and if the time is anytime after 7pm - 11:59 I want it to return 7pm. So far I have:

UPDATE tablename SET TktOpenCoverageTime = IF(TicketOpenTime > DATEPART(hh, TicketOpenDateTime)),'7PM'
ELSE MAX(TicketOpenTime, DATEPART(hh, TicketOpenTime)

I know it is not near right, but I think I'm on the right path.

X002548
Not Just a Number

15586 Posts

Posted - 2009-09-29 : 11:51:54

Please post some sample data and expected results...

Read the hint link in my sig

I'm sure if you do that, You'll get an answer in seconds

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 12:57:05

Here is how I want it to look when I am done:

TktOpenTime TktOpenCoverageTime

10:21AM 10:21AM
2:30AM 5:00AM
8:00PM 7:00PM
10:00PM 7:00PM
1:15PM 1:15PM

That is what I want my End Result to be based on the TicketOpenDateTime.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-29 : 13:03:07

I'm not sure of your actual column names, but maybe something like:

CASE 
	WHEN DATEPART(HOUR, TicketOpenDateTime) >= 0 
		AND DATEPART(HOUR, TicketOpenDateTime) < 5
		THEN '5:00AM'
	WHEN DATEPART(HOUR, TicketOpenDateTime) >= 19 
		THEN '7:00PM'
	ELSE TktOpenTime  
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 13:32:27

whats the datatype of TktOpenTime? assuming its varchar, i think it should be


UPDATE tablename SET TktOpenCoverageTime =CASE WHEN DATEPART(hh,CONVERT(datetime,TktOpenTime)) BETWEEN 0 AND 5 THEN '5:00AM'
                                               WHEN DATEPART(hh,CONVERT(datetime,TktOpenTime)) BETWEEN 19 AND 23 THEN '7:00PM'
                                               ELSE TktOpenTime
                                          END

jaji03
Starting Member

5 Posts

Posted - 2009-09-29 : 14:03:54

Hello,

I'm assuming that TktOpenTime is datepart (in military time)....

Just to show you what I did, I created a temp table (called #tmp) with just some random dates. So, this SELECT statement is just converting the dates to the dates in the criteria. This is what I will use in the CASE statements:

SELECT
TktOpenCoverageTime,
DATEADD(hh,0,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime)),--Criteria for 12am
DATEADD(hh,5,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime)),--Criteria for 5am
DATEADD(hh,19,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime)),--Criteria for 7pm
DATEADD(ss,1439,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime))--Criteria for 11:59pm
FROM #tmp

CASE statement should look like this:
SELECT
TktOpenCoverageTime,
ChangedTime=
CASE
WHEN TktOpenCoverageTime BETWEEN (DATEADD(hh,0,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime))) AND (DATEADD(hh,6,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime)))
THEN DATEADD(hh,5,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime))
WHEN TktOpenCoverageTime BETWEEN (DATEADD(hh,19,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime))) AND (DATEADD(hh,23,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime)))
THEN DATEADD(hh,19,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime))
ELSE TktOpenCoverageTime
END
FROM #tmp

Hope this helps... :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 14:09:39

quote:
Originally posted by jaji03

Hello,

I'm assuming that TktOpenTime is datepart (in military time)....

Just to show you what I did, I created a temp table (called #tmp) with just some random dates. So, this SELECT statement is just converting the dates to the dates in the criteria. This is what I will use in the CASE statements:

SELECT
TktOpenCoverageTime,
DATEADD(hh,0,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime)),--Criteria for 12am
DATEADD(hh,5,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime)),--Criteria for 5am
DATEADD(hh,19,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime)),--Criteria for 7pm
DATEADD(ss,1439,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime))--Criteria for 11:59pm
FROM #tmp

CASE statement should look like this:
SELECT
TktOpenCoverageTime,
ChangedTime=
CASE
WHEN TktOpenCoverageTime BETWEEN (DATEADD(hh,0,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime))) AND (DATEADD(hh,6,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime)))
THEN DATEADD(hh,5,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime))
WHEN TktOpenCoverageTime BETWEEN (DATEADD(hh,19,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime))) AND (DATEADD(hh,23,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime)))
THEN DATEADD(hh,19,CAST(CONVERT(char(10),TktOpenCoverageTime,101) as datetime))
ELSE TktOpenCoverageTime
END
FROM #tmp

Hope this helps... :-)

datepart? do you mean datetime? I think then it should have a date value also which is not case as per sample data

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 14:13:40

Thanks. This does return the Hour of the Start Time I need, now to add in the minutes and seconds to that wouldnt I just add into the ELSE a DATEPART? or a DATEADD?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 14:19:47

did you try my method?

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 14:20:37

Yes. That is what I used, this returns to me the date and the hour of the time. I want the full time, not just the hour.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 14:22:23

whats the datatype of TktOpenTime & TktOpenCoverageTime?

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 14:25:25

TktOpenTime is varchar(20) and TktOpenCoverageTime is datetime.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 14:31:10

then why do you think it returns only hourpart? what do you get in TktOpenCoverageTime for 10:21 AM?

jaji03
Starting Member

5 Posts

Posted - 2009-09-29 : 14:34:52

[/quote]
datepart? do you mean datetime? I think then it should have a date value also which is not case as per sample data
[/quote]

Sorry...Yes, I meant DateTime datatype

It will also work for varchar as long as time is military time

just FYI: The reason I used the DATEADD function is to account for minutes..not just the hour part.

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 14:34:54

The return I get in that column would be 2009-9-14 10:00:00AM.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources