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.intAssignedPersonnelID),

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)