• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

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))
0
kdeutsch
Asked:
kdeutsch
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
change:
min(p.dtattendance) + '-' + max(p.dtattendance)
into:
convert(varchar(10), min(p.dtattendance), 120) + '-' + convert(varchar(10), max(p.dtattendance) , 120)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, for the dd.mon.yyy:
convert(varchar(10), min(p.dtattendance), 106) + '-' + convert(varchar(10), max(p.dtattendance) , 106)

Open in new window

0
 
kdeutschAuthor Commented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
kdeutschAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
 
kdeutschAuthor Commented:
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.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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