varchar "1/2" convert to date 1

[emcevoy]

Hi

I have a table that was created where the date is stored as a m/d and this is causing me real grief. I need to do a look up to it and test this against a real datetime field in a stored procedure.

I need to put add this table and link on this goofy field to another table in the stored proc.

I am kinda new at this and believe me, I didn't create this monster


thanks for all your help people!

Eileen McEvoy
Authorized Crystal Engineer
Crystal Reports Consultant and Trainer

http://www.crystalconsulting.ca

emcevoy@crystalconsulting.ca

 

[donutman]

So what can we do for you?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[btgroup]

Something like this might help you split up the field into month and day:

select 
left(yourdatefield,charindex('/',yourdatefield)-1) as month,
substring(yourdatefield, charindex('/',yourdatefield), len(yourdatefield) - charindex('/',yourdatefield)+1) as day
from yourtable
where charindex('/',yourdatefield) > 0

 

[vongrunt]

There is no year. So I guess it must be somehow reconstructed from varchardate. Say, if m/d is less than current m/d then take current year, otherwise take previous one.

Of course this won't work for data older than one year, in which case original author deserved to be tortured in public .

 

[emcevoy]

Hi

Vongrut, I so agree with you on the torture on this one.

The m/d is every day of the year with no date attached. the data in the table has what the equipment did for that day and the table which has the dreded m/d field has what the targets were for that day.

this is the stored proc I am starting with.



CREATE PROCEDURE dbo.sp_Equipday
@Equip varchar(10)
, @PeriodType varchar(10)
, @PeriodStart smalldatetime
, @PeriodEnd smalldatetime
AS
SET DATEFIRST 1
-- if the @PeriodStart or @PeriodEnd is null, then change these values to the default values
-- if the @PeriodType is null, then change this value to the default value (Month)
-- if the @Equip is null, change this value to All

--SET @EndDate = @Month +'-01-'+ @Year


IF @Equip IS NULL
BEGIN SET @Equip = 'ALL' END
IF @PeriodType IS NULL
BEGIN SET @PeriodType = 'MONTH' END
IF @PeriodEnd IS NULL
BEGIN
IF UPPER(@PeriodType) = 'MONTH'
BEGIN
SELECT @PeriodEnd = CAST(MONTH(GETDATE())AS varchar(2))+'/01/'+CAST(YEAR(GETDATE()) AS varchar(4))
SELECT @PeriodEnd = DATEADD(Day,-1,@PeriodEnd)
END
IF UPPER(@PeriodType) = 'YEAR'
BEGIN
SELECT @PeriodEnd = '01/01/'+CAST(YEAR(GETDATE()) AS varchar(4))
SELECT @PeriodEnd = DATEADD(Day,-1,@PeriodEnd)
END
IF UPPER(@PeriodType) IN ('WEEK','DAY')
BEGIN
SELECT @PeriodEnd = GETDATE()
SELECT @PeriodEnd = DATEADD(Day,-1,@PeriodEnd)
END
END
IF @PeriodStart IS NULL OR @PeriodStart >= @PeriodEnd
BEGIN
IF UPPER(@PeriodType) = 'MONTH'
BEGIN
SELECT @PeriodStart = DATEADD(Month,-6,@PeriodEnd)
SELECT @PeriodStart = DATEADD(Day,-1,@PeriodStart)
END
IF UPPER(@PeriodType) = 'YEAR'
BEGIN
SELECT @PeriodStart = '01/01/'+CAST(YEAR(GETDATE()) AS varchar(4))
SELECT @PeriodStart = DATEADD(year,-5,@PeriodStart)
END
IF UPPER(@PeriodType) = 'WEEK'
BEGIN
SELECT @PeriodStart = DATEADD(Day,-34,@PeriodEnd)
END
IF UPPER(@PeriodType) = 'DAY'
BEGIN
SELECT @PeriodStart = DATEADD(Day,-6,@PeriodEnd)
END
END
--SELECT @Equip, @PeriodType, @PeriodStart, @PeriodEnd

SELECT
A.tblEquipRealName AS Equip
, HD.str_StartTime AS StartTime
, HD.str_EndTime AS EndTime
, HD.num_RunHrs AS RunHours
, HD.date_EquipDay + ISNULL(CAST(I.str_IncidentTime AS smalldatetime),'1900-01-01') AS IncidentDateTime
, CASE WHEN UPPER(@PeriodType) = 'YEAR' THEN DATEPART(year,HD.date_EquipDay )
WHEN UPPER(@PeriodType) = 'MONTH' THEN DATEPART(month,HD.date_EquipDay )
WHEN UPPER(@PeriodType) = 'WEEK' THEN DATEPART(week,HD.date_EquipDay )
WHEN UPPER(@PeriodType) = 'DAY' THEN DATEPART(day,HD.date_EquipDay )
ELSE 0 END AS Period
, CASE WHEN UPPER(@PeriodType) = 'YEAR' THEN DATENAME(year,HD.date_EquipDay )
WHEN UPPER(@PeriodType) = 'MONTH' THEN SUBSTRING(DATENAME(m,HD.date_EquipDay ),1,3)
WHEN UPPER(@PeriodType) = 'WEEK'
THEN CONVERT(varchar(10),DATEADD(DAY,-DATEDIFF(DAY,'Jan 1, 2001',HD.date_EquipDay )%7,HD.date_EquipDay ),6)
WHEN UPPER(@PeriodType) = 'DAY' THEN CONVERT(varchar(10),HD.date_EquipDay,6)
ELSE DATENAME(m,HD.date_EquipDay) END AS PeriodName
, I.int_IncidentDuration AS IncidentDuration
, IT.str_EquipIncidentTypeDescrip AS IncidentType
, IC.ID_EquipIncidentCode AS IncidentCode
, IC.str_EquipIncidentDescrip AS IncidentDescription
, I.str_IncidentComments AS IncidentComments
FROM
tblEquipment AS A
JOIN tblEquipDay AS HD
ON A.tblEquipRealName = HD.ID_EquipID
JOIN tblEquipIncidents AS I
ON HD.ID_EquipID = I.ID_EquipID
AND HD.ID_EquipDay = I.ID_EquipDay
JOIN tblEquipIncidentCode AS IC
ON I.ID_IncidentCode = IC.ID_EquipIncidentCodeID
JOIN tblEquipIncidentType AS IT
ON IT.ID_EquipIncidentType = IC.ID_EquipIncidentType
WHERE
HD.date_EquipDay + ISNULL(CAST(I.str_IncidentTime AS smalldatetime),'1900-01-01') BETWEEN @PeriodStart-1 AND @PeriodEnd
AND (CASE WHEN UPPER(@Equip) = 'ALL' THEN A.tblEquipRealName ELSE UPPER(@Equip) END) = UPPER(A.tblEquipRealName)
ORDER BY
HD.date_EquipDay + ISNULL(CAST(I.str_IncidentTime AS smalldatetime),'1900-01-01')
GO

*************************************

and this other table Targetable with fields

targetdate - nvarchar 5 ie 1/2 for jan 2
targethours - float 8

now i need to match up HD.date_EquipDay which is a full datetime field with targetdate

as you can see that we prompt for which equipment, group of day, month or week. and we give the date start and ends.
I need to pull the targetdate for only that period.

if anyone has any guidance they could provide would be awesome. As I said, I am a bit newer at this depth.

thanks again all!!

Eileen McEvoy
Authorized Crystal Engineer
Crystal Reports Consultant and Trainer

http://www.crystalconsulting.ca

emcevoy@crystalconsulting.ca

 

[vongrunt]

Converting "m/d" into date is easy task:

set dateformat mdy
convert(datetime, '1/2' + '/' + convert(varchar(4), year_value))

Just replace year_value with YEAR(GETDATE()) or YEAR(some_db_field) depending on logic. But again, at least two problems may happen:

- invalid "date" ('13/2' or 'foo/blah' or even ''). Query crashes with syntax error
- tables contain data that span across more than one year. January 2nd... 2003? 2004?

If you cannot get rid of crippled varchar date column, for future purposes I'd recommend you to add column real_date datetime, update it w/ existing values and write trigger FOR INSERT, UPDATE that keeps this new column "in sync" with m/d column.