Convert to date

I am using SQL Server 2005.
I have a table as below:
LCode varchar(5)
PeriodFrom varchar(8)
PeriodTo varchar(8)
The data stored in this table looks like this:
E1 01-Jan 30-Jun
E2 01-Jul 31-Dec
How to query to check whether a date is lying between Period of E1 or
E2. For example, if I have a date like 03-Mar, I want to search in the
above table to know in which period it is falling.
I have taken PeriodFrom and PeriodTo as varchar.RP -
Is there any particular reason on why you can not make the PeriodFrom and
PeriodTo a DateTime Field. I think it might be easy if you can make it a
DateTime field.
If it is not possible, one way to do this that I can think of immediately is
that concat an arbitrary year to the PeriodFrom and PeriodTo field and then
convert it to the date field, an example of the query would be:
DECLARE @.Check AS VARCHAR(25)
DECLARE @.Year AS VARCHAR(8)
SET @.Check = '03-Mar'
SET @.Year = '2000'
SET @.Check = @.Check + '-' + @.Year
SELECT LCode
FROM dbo.Test
WHERE @.Check >= CAST(PeriodFrom + '-' + @.Year AS DATETIME) AND
@.Check <= CAST(PeriodTo + '-' + @.Year AS DATETIME)
Hope that works
Lucas
"RP" wrote:
> I am using SQL Server 2005.
> I have a table as below:
> LCode varchar(5)
> PeriodFrom varchar(8)
> PeriodTo varchar(8)
> The data stored in this table looks like this:
> E1 01-Jan 30-Jun
> E2 01-Jul 31-Dec
> How to query to check whether a date is lying between Period of E1 or
> E2. For example, if I have a date like 03-Mar, I want to search in the
> above table to know in which period it is falling.
> I have taken PeriodFrom and PeriodTo as varchar.
>|||I don't want the year part. Only date-month need to be stored and
that's why I used varchar for Data field.
On Sep 5, 2:48 am, Lucas Kartawidjaja
<LucasKartawidj...@.discussions.microsoft.com> wrote:
> RP -
> Is there any particular reason on why you can not make the PeriodFrom and
> PeriodTo a DateTime Field. I think it might be easy if you can make it a
> DateTime field.
> If it is not possible, one way to do this that I can think of immediately is
> that concat an arbitrary year to the PeriodFrom and PeriodTo field and then
> convert it to the date field, an example of the query would be:
> DECLARE @.Check AS VARCHAR(25)
> DECLARE @.Year AS VARCHAR(8)
> SET @.Check = '03-Mar'
> SET @.Year = '2000'
> SET @.Check = @.Check + '-' + @.Year
> SELECT LCode
> FROM dbo.Test
> WHERE @.Check >= CAST(PeriodFrom + '-' + @.Year AS DATETIME) AND
> @.Check <= CAST(PeriodTo + '-' + @.Year AS DATETIME)
> Hope that works
> Lucas