June 3, 2009 at 1:32 pm
I have a table that holds month and day for each start period for a clinic.
SO one clinic may start each year april 1st and end on march 31 in the database these (start and end date though by themselves are not technically dates) are stored in the db as Varchar for instance
the start date for above would be in the DB like 4/01 and the end date would be like 3/31
I have two issues one I can't get this to convert to date time even if i add a datepart (year,getdate) to my query. Secondly since the time goes from one year to the next I am not sure exactly how to handle it. Some clinics may start on april of one year and end in march of the next. Others may start and end in the same yeat Jan-december. now I have this but I get conversion failed.
I need more than just this to solve my second issue as well.
between (select convert (smalldatetime,clinicstartdate + DatePart(Year,GetDate())) from clinics where clinicid=@ClinicID ) and
select convert (smalldatetime,clinicEnddate + DatePart(Year,GetDate())) from clinics
June 3, 2009 at 3:01 pm
here's how i would do it:
ignore the end date, and calculate it based on the clinicstartdate.
i assume that if you take the year() of today, plus the month/day, that is the start date.
then all you do is DATEADD 1 year to it, then subtract one day to get the end if year.
you didn't actually give a format example of your data, but here's a guess:
with MyCTE As (SELECT '01/01' AS clinicstartdate UNION ALL
SELECT '01/15' AS clinicstartdate UNION ALL
SELECT '02/28' AS clinicstartdate UNION ALL
SELECT '03/01' AS clinicstartdate UNION ALL
SELECT '04/01' AS clinicstartdate UNION ALL
SELECT '04/15' AS clinicstartdate UNION ALL
SELECT '06/30' AS clinicstartdate )
--
select convert(datetime,clinicstartdate + '/2009') as realclinicstartdate,
DATEADD(day,-1,DATEADD(year,1,convert(datetime,clinicstartdate + '/2009') )) as CalculatedEndDate
from MyCTE
--results
realclinicstartdate CalculatedEndDate
----------------------- -----------------------
2009-01-01 00:00:00.000 2009-12-31 00:00:00.000
2009-01-15 00:00:00.000 2010-01-14 00:00:00.000
2009-02-28 00:00:00.000 2010-02-27 00:00:00.000
2009-03-01 00:00:00.000 2010-02-28 00:00:00.000
2009-04-01 00:00:00.000 2010-03-31 00:00:00.000
2009-04-15 00:00:00.000 2010-04-14 00:00:00.000
2009-06-30 00:00:00.000 2010-06-29 00:00:00.000
Lowell
June 3, 2009 at 3:12 pm
I'm guessing that the required start date is for the current year and that the varchar column that stores the month and day is consistently in the format M/D where M is the month number (1 - 12) and D is the day of the month (1 - 31).
First some test data.
CREATE TABLE #Test (
clinicStartDate varchar(5),
clinicEndDate varchar(5)
)
INSERT #Test (clinicStartDate, clinicEndDate)
SELECT '4/01', '3/31' UNION ALL
SELECT '1/01', '12/31'
The query below uses a CTE to parse the month/day strings for the start and end of the clinic into pairs of integers representing the month and day of month. These parsed integers are then used to derive the datetime values. If the clinic end month and day is earlier in the year than the clinic start month and day then the clinic end date is assumed to be in the following year.
;WITH cteDatePart (StartMonth, StartDay, EndMonth, EndDay) AS (
SELECT
CONVERT(int, SUBSTRING(clinicStartDate, 1, CHARINDEX('/', clinicStartDate) - 1)),
CONVERT(int, SUBSTRING(clinicStartDate, CHARINDEX('/', clinicStartDate) + 1, 5)),
CONVERT(int, SUBSTRING(clinicEndDate, 1, CHARINDEX('/', clinicEndDate) - 1)),
CONVERT(int, SUBSTRING(clinicEndDate, CHARINDEX('/', clinicEndDate) + 1, 5))
FROM #Test
)
SELECT
DATEADD(year,
DATEDIFF(year, 0, GETDATE()),
DATEADD(month, StartMonth - 1, StartDay - 1)
) AS ClinicStart,
DATEADD(year,
DATEDIFF(year, 0, GETDATE())
+ CASE WHEN (StartMonth > EndMonth OR (StartMonth = EndMonth AND StartDay > EndDay)) THEN 1 ELSE 0 END,
DATEADD(month, EndMonth - 1, EndDay - 1)
) AS ClinicEnd
FROM cteDatePart
June 4, 2009 at 6:27 am
thanks Guys I ended up using eights Idea and created a function like so
alter Function [dbo].[funConvertClinicDates] (@ClinicID int,@Year varchar(5))
Returns Table
AS
Return
WITH cteDatePart (ClinicID,StartMonth, StartDay, EndMonth, EndDay) AS (
SELECT ClinicID,--clinicstartdate,clinicenddate,
CONVERT(int, SUBSTRING(clinicStartDate, 1, CHARINDEX('/', clinicStartDate) - 1)),
CONVERT(int, SUBSTRING(clinicStartDate, CHARINDEX('/', clinicStartDate) + 1, 5)) ,
CONVERT(int, SUBSTRING(clinicEndDate, 1, CHARINDEX('/', clinicEndDate) - 1)),
CONVERT(int, SUBSTRING(clinicEndDate, CHARINDEX('/', clinicEndDate) + 1, 5))FROM Clinics --where clinicID=@ClinicID
)
SELECT ClinicID,
DATEADD(year,
DATEDIFF(year, 0, @Year),
DATEADD(month, StartMonth - 1, StartDay - 1)
) AS ClinicStart,
DATEADD(year,
DATEDIFF(year, 0, @Year)
+ CASE WHEN (StartMonth > EndMonth OR (StartMonth = EndMonth AND StartDay > EndDay)) THEN 1 ELSE 0 END,
DATEADD(month, EndMonth - 1, EndDay - 1)
) AS ClinicEnd
FROM cteDatePart
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply