Is there a time equivalent for DATEFIRST

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)

Is there a time equivalent for DATEFIRST

Author

Topic

tjwent69
Starting Member

30 Posts

Posted - 2009-05-06 : 11:28:32

I cannot seem to find this in the BOL. Here is what I need.

We control our date here in the dB. Everyday at 5:00 it becomes the next day.

For reports I need to set 5/5/2009 17:00:00.000PM -- which is actually 5/4/2009 17:00:00.000PM --
to be 5/5/2009 00:00:00.000AM then do date time comparrisons.

Right now when I compare my invoice date 5/5/2009 17:00:00.000PM to my pickup date 5/5/2009 8:00:00.000AM I am getting -15 hours. (which is correct, but not what I want)

Is there Set TIMEFIRST(17:00:00.000)?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-06 : 11:41:21

Can you use a dateadd?


Delcare @Datetime datetime,@NDatetime Datetime
set @Datetime = '5/5/2009 17:00:00.000PM'

select @NDateTime = dateadd(h,7,@Datetime) -- Will accomidate the next day
select dateadd(dd,0, datediff(dd,0,@NDateTime)) -- Will get you just the date not the time

--Let me know if you need more clearification

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

tjwent69
Starting Member

30 Posts

Posted - 2009-05-08 : 10:06:02

I used some dateadd and a case to get what I want but it seems unsophistocated.

Select
convert(varchar,o.InvoiceDate, 22) as InvoiceDate
, datediff(second, o.CreateDate, o.InvoiceDate) / 3600 as OrderToInvoicehours
, (datediff(second, o.CreateDate, o.InvoiceDate) % 3600) / 60 as OrderToInvoiceMinutes
, convert(varchar,o.EstimatedPickupDate, 22) as PickupDate
, datediff(second, Case when convert(varchar(8),o.InvoiceDate,8) > '17:00:00.000' and convert(varchar(8),o.InvoiceDate,8) <= '23:59:59.9999'
then convert(varchar,dateadd(dd, -1,o.InvoiceDate), 22)
Else convert(varchar,o.InvoiceDate, 22)end , EstimatedPickupDate) / 3600 as InvoiceToCustomerhours
, (datediff(second, Case when convert(varchar(8),o.InvoiceDate,8) > '17:00:00.000' and convert(varchar(8),o.InvoiceDate,8) <= '23:59:59.9999'
then convert(varchar,dateadd(dd, -1,o.InvoiceDate), 22)
Else convert(varchar,o.InvoiceDate, 22)end , EstimatedPickupDate) % 3600) / 60 as InvoiceToCustomerMinutes

Well, thats the code. Thanks for the reply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 10:44:22

why converting to varchar? did you try Vinnie's solution?

tjwent69
Starting Member

30 Posts

Posted - 2009-05-08 : 16:06:17

I took a look at it and I understand it. I actually need the minutes too. Setting the date back one day to do the hour and minute calculation got the jobe done.

Since I figured out my 'little time issue' They through another curve at me.

The point of the report is to see how much time a sales person is giving the warehouse to fill the order. Diff in hours and Minutes from the invoicedate to the estimatedpickupdate. I have that in the report now. But... the warehouse doesn't work at night. So anything invoiced after 5 isn't even looked at until 6 am. So 6 am is the real time for all invoices sent in from 17:00 to 05:59.

I think I need to calculate the datediff in minutes of invoices from 17:00 to 05:59 then add those minutes to the original invoicedate.

Before:

05/05/09 11:07:49 PM calc min diff is 413
05/05/09 02:17:00 AM calc min diff is 223
05/05/09 11:41:15 AM no calc because it isn't between 17:00 and 05:59

Want to have After
05/05/09 06:00:00 AM
05/05/09 06:00:00 AM
05/05/09 11:41:15 AM

When I do this:
datediff(mi, Convert(varchar(8),o.InvoiceDate,14), '05:59:59.000') as minutesdiff

I get -1028 for minutesdiff

Problem 1 - it should be 413 min.
Problem 2 - it's negative which was the problem that started the original post.

As I am typing and thinking and working this whole thing out I am thinking about your question about the convert to varchar. I use the var char so I can control the formatting on the report. Admittedly I am terrible at datetimes and I have to break it down and put it back together to understand my hours and minutes columns on the report. Can the varchar converts be the source of my problems?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-08 : 20:38:07

Is this what you're looking to do?





--select @NDateTime = dateadd(h,7,@Datetime) -- Will accomidate the next day
--select dateadd(dd,0, datediff(dd,0,@NDateTime)) -- Will get you just the date not the time



DECLARE @Mydates Table(d datetime)
Insert into @Mydates select '5/5/2009 17:00:00.000PM'
Insert into @Mydates select '05/05/09 11:07:49 PM'
Insert into @Mydates select '5/5/2009 2:17:00.000AM'
Insert into @Mydates select '5/5/2009 11:45:00.000AM'


Select *
, 
case 
when datepart(hour,a.d) >= 17 then dateadd(hour,6,dateadd(dd,1, datediff(dd,0,a.d)))
when datepart(hour,a.d) <= 5 then dateadd(hour,6,dateadd(dd,0, datediff(dd,0,a.d)))
else a.d
end
from
@Mydates a

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

tjwent69
Starting Member

30 Posts

Posted - 2009-05-09 : 08:42:28

Yes, that is absolutly brilliant.

Only work with the hour. Find midnight add 6. Change or leave date depending on the time.

Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources