kdeutsch
asked on
Combine 2 Dates into 1
In my select statement I have 2 dates and I tried using a (+ '-' +) to add the 2 fields together but it kept giving me this error (The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.) The following is my sql code, what I am trying to do is put the dates together and try to format them as 26-30 june 2008, right now they would show up as 26-june-2008 and 30-june-2008 in my labels on aspx page. Is this possible or not???? Thanks
select s.sidstrName_IND,
s.sidstrST_ADDR,
s.sidstrADDR_CITY,
s.sidstrSTATES_US,
s.sidstrZIP_CODE,
s.sidstrGR_ABBR_CODE,
s.sidstrPAY_GR,
cast(datediff(year, siddatPEBD, getdate()) as varchar(20)) as PGYrs,
count(p.intAssignedPersonn elID),
min(p.dtattendance) + '-' + max(p.dtattendance) as EDate (Problem Area ??????)
from cms.dbo.tblSIDPERS as s INNER JOIN
tblAssignedPersonnel as p on s.sidstrSSN_SM = p.strSSN
where p.bitPresent = 1 And
p.intDrillStatus = 2 And
p.dtPayProcessed Is Not Null and
p.dtPaid Is null and
p.intUICID in (select intUICID from tblUIC where intTaskForceID = '13' and strUIC = 'PU3B1') and
p.strSSN = '474968040'
group by s.sidstrName_IND, s.sidstrST_ADDR, s.sidstrADDR_CITY, s.sidstrSTATES_US, s.sidstrZIP_CODE, s.sidstrGR_ABBR_CODE, s.sidstrPAY_GR, cast(datediff(year, siddatPEBD, getdate()) as varchar(20))
select s.sidstrName_IND,
s.sidstrST_ADDR,
s.sidstrADDR_CITY,
s.sidstrSTATES_US,
s.sidstrZIP_CODE,
s.sidstrGR_ABBR_CODE,
s.sidstrPAY_GR,
cast(datediff(year, siddatPEBD, getdate()) as varchar(20)) as PGYrs,
count(p.intAssignedPersonn
min(p.dtattendance) + '-' + max(p.dtattendance) as EDate (Problem Area ??????)
from cms.dbo.tblSIDPERS as s INNER JOIN
tblAssignedPersonnel as p on s.sidstrSSN_SM = p.strSSN
where p.bitPresent = 1 And
p.intDrillStatus = 2 And
p.dtPayProcessed Is Not Null and
p.dtPaid Is null and
p.intUICID in (select intUICID from tblUIC where intTaskForceID = '13' and strUIC = 'PU3B1') and
p.strSSN = '474968040'
group by s.sidstrName_IND, s.sidstrST_ADDR, s.sidstrADDR_CITY, s.sidstrSTATES_US, s.sidstrZIP_CODE, s.sidstrGR_ABBR_CODE, s.sidstrPAY_GR, cast(datediff(year, siddatPEBD, getdate()) as varchar(20))
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
OK, I found a little thing with sql that helps me with my problem of consolidating the 2 dates into one date range. What I don't know is if it will eventually give me any hiccups in my program. I took your coversion statement but for the first date changed the varchar to (2) so that it only shows the first 2 days of the date, then combined them so it looks like below.
convert(varchar(2), min(p.dtattendance), 106) + '-' + convert(varchar(18), max(p.dtattendance) , 106)
This gives me a date of 26-30 June 2008. This solves my problem but will it give me hiccups latter on or is this good. Thanks
convert(varchar(2), min(p.dtattendance), 106) + '-' + convert(varchar(18), max(p.dtattendance) , 106)
This gives me a date of 26-30 June 2008. This solves my problem but will it give me hiccups latter on or is this good. Thanks
ASKER
Ok I see what it does by doing it this way if a person only served one date it comes up as such
22-22 May 2008. is there a way to format this more to take care of this problem. Thanks
22-22 May 2008. is there a way to format this more to take care of this problem. Thanks
question: what if the dates where 30/06 and 01/07 ? ...
would be 30-01 July 2008 ... not really simple :)
next step: 30/12/2007 - 01/01/2008 ....
I would put that "logic" eventually in a function...
would be 30-01 July 2008 ... not really simple :)
next step: 30/12/2007 - 01/01/2008 ....
I would put that "logic" eventually in a function...
ASKER
AH, see what you mean, if they go into another month it will look wrong, don't know that we have had that problem yet, as far as the year changeover, we won't have that problem because of FY constraints.
min(p.dtattendance) + '-' + max(p.dtattendance)
into:
convert(varchar(10), min(p.dtattendance), 120) + '-' + convert(varchar(10), max(p.dtattendance) , 120)