Returning Date Values based on Times

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

SQL Server Development (2000)

Returning Date Values based on Times

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-11-14 : 13:45:05

I have the following very simple query to return a list of unique ticket dates:

Select Distinct(Convert(nvarchar,TicketDate,110) as TicketDate
From TicketHeader

The complication comes in when I try to add an Open Time and Close Time. In theory, a business may open at 8am and close at 2am. So for an example, say the business ran tickets yesterday until 2am. When I run this simple query, it returns 11/13/2007 and 11/14/2007 because the ticketdates for the tickets ran after midnight have the 14th date BUT they should be on the 13th business because they were executed before 2am.

What I would like to happen is that the query above only return the 13th becuase the 14ths tickets are on the 13th. The problems I have had (but not limited to) are getting results to show even if tickets are only rang up on the 14th at 1am. Say for example last night, they closed their business and then found 3 tickets they needed to key. So they keyed those three tickets at 1am. They are still included on the 13th business but were rang up on the 14th. I would want the result set to still show the 13th (And everything I try is making it not display at all). I dont know if what I am trying to do is possible becuase I realize I am trying to manipulate the dates returned, but it is the only way I can get it to process those tickets on the 14th correctly. Any suggestions are greatly appreciated.

JAdauto

I built a simple table with just ID and TicketDate as the fields:

CREATE TABLE [dbo].[TicketHeader] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TicketDate] [datetime] NULL
) ON [PRIMARY]
GO

Here are some sample tickets with 2 on the 13th and one on the 14th at 1am.

Insert into TicketHeader (TicketDate) Values ('November 13, 2007 5:00:00 PM')
Insert Into TicketHeader (TicketDate) Values ('November 13, 2007 7:00:00 PM')
Insert Into TicketHeader (TicketDate) Values ('November 13, 2007 1:00:00 AM')

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-14 : 13:47:49

if tickets sold before 2am belong to the previous day, just substract 2 hours from the date
in your aggregations use

dateadd(hh, -2, TicketDate)

instead of TicketDate

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-11-14 : 14:08:18

It may not always be 2am. The businesses will set their own close time. 2am was just an example. I guess I could do a case statement for every hour after midnight until the open hour? It seems like there is a way I could do a Case or If statement based on the TicketDate using the close time. But alas, I cannot figure anything else out.

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-11-14 : 14:09:12

I should have posted this under Transact SQL I guess. Sorry.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-14 : 14:24:25

quote:
Originally posted by JAdauto

It may not always be 2am. The businesses will set their own close time. 2am was just an example. I guess I could do a case statement for every hour after midnight until the open hour? It seems like there is a way I could do a Case or If statement based on the TicketDate using the close time. But alas, I cannot figure anything else out.

You have to know what your own business rules are before we can help you write a query. Just how will you know what the rules are?

CODO ERGO SUM

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-11-14 : 14:51:20

The business sets their own business rules. Our job is to provide the TOOLS and SOFTWARE to use in their business. They key in their open and close times in a field that we store. In all other queires I have used a clause like

Where TicketDate >'November 13, 2007 8:00:00AM' and TicketDate < 'November 14, 2007 2:00:00 AM'

I build the statement dynamically with the clubs closing time from the field. However, in those queries, I know a specific date that I want to get. In this query I want to return a list of dates that are available in TicketHeader. I cannot figure out how to do that or if it is possible

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-14 : 15:18:44

dateadd(hh, - BusinessRules.DayCloseHour, TicketDate)
:)

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-11-15 : 10:51:26

I feel like I am so close. Does anyone have any suggestions for the following:

Select
Case Convert(nvarchar,TicketDate,108)
When Convert(nvarchar,TicketDate,108) > '02:00:00 AM'
Distinct(Convert(nvarchar,TicketDate,110))
Else
DateAdd(dd, -1 Distinct(Convert(nvarchar,TicketDate,110))
End
From TicketHeader

It may not work, but this is the closest I have gotten in 24 hours to getting what I need returned, only I am getting a syntax error. on line 3. Can I do a > on the case statement?
Thanks,
JAdauto

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-15 : 12:05:26

Are you aware that '02:00:00 PM' < '02:00:01 AM' ???
It is much safer to use datepart functions.

Also, why did you reject my solution?

Regarding the syntax error, remove first Convert(nvarchar,TicketDate,108)
There are 2 different case statements:
case expr when e1 then expr1 when ... end
and
case when condition1 then expr1 ... end

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-11-15 : 14:43:08

quote:
Originally posted by evilDBA

Are you aware that '02:00:00 PM' < '02:00:01 AM' ???
It is much safer to use datepart functions.

Also, why did you reject my solution?

I did not reject it. I tried, but maybe I just did not get what you were saying to do. (Sometimes it has to be spelled out for me) So I was trying to add a case statement for every hour. Then I realized that a club could very easily close their business at 1:30am or 1:45am. Then how do I build a SQL statement on that. So I was having to figure out how much passed midnight every time was and then build that into the DateAdd part of the SQL statement. In then end, I have a table that has probably 200-500 records in it. I need a list of DISTINCT dates for those tickets. And it is doing that currently. But if the ticket is rang up at 1am, I dont want it to display the next date. I dont have a date range to put in a Where like I have before. This is the first time I have had to display all the dates that are available. So I thought if i Know what the close time is, then I can build that into the SQL statement in the Case portion. If tickets come before 2am (or whatever time they have setup), then I will display the date - 1. If tickets come after 2am so I know those tickets are on the correct date because technically teh club is closed from 2am - 8am. Or am I way off base in this logic???

[/quote]
Regarding the syntax error, remove first Convert(nvarchar,TicketDate,108)
There are 2 different case statements:
case expr when e1 then expr1 when ... end
and
case when condition1 then expr1 ... end
[/quote]
I am not sure what first Convert you are talking about?? I removed the one after the Select and also after the Case. Neither way is working, but that may just be me not understanding again.

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-15 : 15:32:38

1. If your business is closed at 1:45am, then you need to store the number of minutes after midnight, in that case, 60+45 = 105.
Then you substract that number of minutes from your day/time
dateadd(mm, -105, YourDate)
1:40am will be shifted to 23:55 *PREVIOUS DAY*, while 1:50am to 0:05 NEXT DAY
That is exactly what you want
Just need to get rid of the time portion of the datetime and ready for GROUP BY

2. Regarding syntax
Select
Case
When Convert(nvarchar,TicketDate,108) > '02:00:00 AM'
then Convert(nvarchar,TicketDate,110)
Else
DateAdd(dd, -1 Convert(nvarchar,TicketDate,110)
End
From TicketHeader

3. Distinct is not a function. You can not write DISTINCT (expr) everywhere
It must be at outer level of the select list.
Also, the same date with different times are different and won't be removed by DISTINCT

Try select distinct convert(varchar,dateadd(mm, -105, YourDate),101) from ....

JAdauto
Posting Yak Master

160 Posts

Posted - 2007-11-15 : 16:50:34

quote:
Originally posted by evilDBA
Try select distinct convert(varchar,dateadd(mm, -105, YourDate),101) from ....

This is not returning the right date. My sample data above has 4 records for 10-13 and 10-14 (all for the 13th business day), but the result set is returning 02-13-1999 and 02-14-1999.

This is what it is actually looks like right now. Is this what you were meaning?

select distinct convert(varchar,dateadd(mm, -105, TicketDate),110)
from ticketheader

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-15 : 17:03:09

mm is months
try
dateadd(minute, -105, YourDate)

Don't be afraid to look up the DATEADD function in SQL Server Books Online.

CODO ERGO SUM

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-16 : 04:07:55

oops, yes, mm means month. Sorry for confusion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources