Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

SQL DateAdd ????

Hi guys!
Not sure if my problem needs DateAdd or not but here is my issue.
I need a stored procedure that will query the date field in a  table and
bring back a record for the current date, a record for the next day, and a record
for the day after that. According to the dates in the field.
Sounds a little confusing but see below.

And to make matters worse....the Shipdate datatype is varchar!
Sequence   ShipDate
--------- --------
3568597	12/22/2009
3570011	12/22/2009
3570482	12/23/2009
3570491	12/23/2009
50994	01/04/2010
51002	01/04/2010

Open in new window

0
abennett10
Asked:
abennett10
2 Solutions
 
vinurajrCommented:
what is the final requirement..?
0
 
PCIIainCommented:
so from that data, and selecting on the date 12/22/2009, you want one of (randomly)
3568597 12/22/2009 and
3570011 12/22/2009

and either one of

3570482 12/23/2009 and
3570491 12/23/2009

and a null return (as there is no record for 12/24/2009)

Or do you mean next  working day rather than next day?
Is there anything you're not telling us which might enable us to choose which of the 4 random combinations your current data gives us is the 'right' answer?
0
 
abennett10Author Commented:
I see I am leaving out alot....

what I am looking for is  the  highest "Sequence" for at least 3 days out, or as many different dates there are. All seperate records
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jogosCommented:
-- to get an idee what it does
select getdate()   -- today
,convert(char,getdate(),101)  -- today as char
,convert(char,getdate() + 2 ,101) -- today + 2 as char
,convert(datetime,convert(char,getdate(),101),101) -- today without hours

-- What you need: where shipdate is today, tomorow and day after tomorow
declare @today as datetime;
declare @todayPlus2 as datetime;
select @today = convert(datetime,convert(char,getdate(),101),101);
select @todayPlus2 = convert(datetime,convert(char,getdate() + 2,101),101);

select *
from x
where convert(datetime,x.shipdate,101) between @today and @todayPlus2;

go
0
 
PCIIainCommented:

Attached code should show the general principle.
select 3568597 as Sequence,  cast('12/22/2009' as varchar(10)) as ShipDate into #tmp
union select 3570011 ,'12/22/2009'
union select 3570482 ,'12/23/2009'
union select 3570491 ,'12/23/2009'
union select 50994   ,'01/04/2010'
union select 51002   ,'01/04/2010'

select max(sequence),cast(shipdate as datetime) as shipdate from #tmp
where cast(shipdate as datetime) >= dateadd(day,datediff(day,'20090101',getdate()),'20090101')
group by cast(shipdate as datetime)
order by cast(shipdate as datetime)

drop table #tmp

Open in new window

0
 
jogosCommented:
OK, Previous was concentrating on
-- converting
-- datetime (whithout hours)
Introducint max() and group by get's you ONE record per date and the highest sequence

-- What you need: where shipdate is today, tomorow and day after tomorow
declare @today as datetime;
declare @todayPlus2 as datetime;
select @today = convert(datetime,convert(char,getdate(),101),101);
select @todayPlus2 = convert(datetime,convert(char,getdate() + 2,101),101);

select x.shipdate, max(sequence)
from x
where convert(datetime,x.shipdate,101) between @today and @todayPlus2;
group by x.Shipdate

go
0
 
jogosCommented:
group by can give a false idee off ordening +  I left a ;

about sollution @PCIIan,
-- cast in SELECT and in GROUP BY are not needed
-- with CAST you trust the automatic date-conversion, it's safer to use CONVERT and provide the format in which the date is , 101 stands for mm/dd/yyyy  US-format
select 3568597 as Sequence
,  cast('12/22/2009' as varchar(10)) as ShipDate into #tmp 
union select 3570011 ,'12/22/2009' 
union select 3570482 ,'12/23/2009' 
union select 3570491 ,'12/23/2009' 
union select 4570482 ,'12/24/2009' 
union select 4570491 ,'12/24/2009' 
union select 5570482 ,'12/25/2009' 
union select 5570491 ,'12/25/2009' 
union select 6570482 ,'12/26/2009' 
union select 7570482 ,'12/27/2009' 
union select 50994   ,'01/04/2010' 
union select 51002   ,'01/04/2010' 
 
declare @today as datetime;
declare @todayPlus2 as datetime;
select @today = convert(datetime,convert(char,getdate(),101),101);
select @todayPlus2 = convert(datetime,convert(char,getdate() + 2,101),101);

select x.shipdate, max(sequence) 
from #tmp as x
where convert(datetime,x.shipdate,101) between @today and @todayPlus2
group by x.Shipdate
order by convert(datetime,x.Shipdate,101)
; 
drop table #tmp;
GO

Open in new window

0
 
PCIIainCommented:
I agree on the group by, probably overkill, but I wanted to cast/convert the item in the select by, as that way whatever is upstream of this query will actually receive a datetime value, making further processing more intuitive....
0
 
jogosCommented:
@PCCian  
I fully agree with the upstream-idea  the less conversions the better (performance). With this 'mantra' in mind my comment was given but indeed probably what shortsighted to this query alone.  

But when in that table the shipdate is put into a varchar I expect them to do so on other places so returning a datetime to compare with a varchar will cause an implicit type-conversion. And there is only one thing worse than putting things in wrong data-types and that is comparing different data-types without explicitly CAST/CONVERT it to the data-type needed.



0
 
jogosCommented:
Off topic
a varchar for a date? unusable for sorting, unless you write it the yyyy/mm/dd - way. Still ask myself after half a century of computers why the IT-world never had the users convinced to addapt a format that triggers less problems (sorting and redional-dependend formats).
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now