Click here to Skip to main content
15,307,418 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi
I have 3 columns like below
ShowName   FDate ToDate IsCurrentYear
SpiderMan  04-01 09-30       0
ICe Man    10-01 04-30       1

Now i wanted book the show which is available for current date
Here spiderman is showed in theater between 04-01-2013 and 09-30-2013+0
ICe Man is showed in theater between 10-01-2013 and 04-30-2013+1
I wanted to show the details which are available for current date
For example if i try to book 03-29-2013 then i wanted to show both spiderman and ice man in my results
if i try to book 10-02-2013 then i wanted to show only Ice man
how to do that one in sql server

I try to get the values like below but i failed
SQL
SELECT *FROM TableName WHERE CONVERT(DateTime,FromMonth,110) > GetDate() OR ToMonth+'-'+(year(getdate())+isCurrentYear)  > GetDate() OR GetDate() BETWEEN CONVERT(DateTime,FromMonth,110) AND ToMonth+'-'+(year(getdate())+isCurrentYear)
Posted
Updated 9-Jul-13 3:25am
v3
Comments
joshrduncan2012 9-Jul-13 9:20am
   
Why did it fail? Just saying "i failed" doesn't help us. What error are you getting, if any?
krishna_goluguri 9-Jul-13 9:26am
   
This is the error
Conversion failed when converting date and/or time from character string.
gvprabu 9-Jul-13 9:25am
   
give the sample data... what is the data structure of ur table?
krishna_goluguri 9-Jul-13 9:27am
   
ShowName FDate ToDate IsCurrentYear
SpiderMan 04-01 09-30 0
ICe Man 10-01 04-30 1
Titanic 11-01 01-31 1
Maciej Los 9-Jul-13 9:27am
   
In my opinion: bad table design!
Re-design table and change FromMonth to ToMonth as date data type.
krishna_goluguri 9-Jul-13 9:28am
   
its from client i cant do anything
Mike Meinz 9-Jul-13 15:29pm
   
Part of your job as a software developer is to go to the client and tell them that the table design has to be changed to use best practices and to minimize the cost of the software development work.
gvprabu 9-Jul-13 9:30am
   
yes abs correct... If we keep the date details in CHAR type, then we will face many problem while convert to Datetime. So change your table structure....
gvprabu 9-Jul-13 9:31am
   
then tel me one thing... what is the format for "04-01" is "MM-DD" or "DD-MM" and how u will use IsCurrentYear Column here?. If any data is wrong like FDate is grater than ToDate then ?
krishna_goluguri 9-Jul-13 9:37am
   
MM-DD FDate is less than Todate
for example consider below
SpiderMan 04-01 09-30 0
means
spider man showed in theater between Apr-01-2013 to sep-30-2013+0 ie sep-30-2013
we need to add iscurrent year to todate if it is 1 then currentyear+1 if it is 0 the currentyear+0
ZurdoDev 9-Jul-13 9:54am
   
You have to concatenate the date onto the FromMonth so that it is an actual date.

It is a best practice to declare database table columns that contain date and/or time type data using the various DateTime data types available in the SQL Server. By doing so, you can use the built-in date comparison capability. By not doing so, you over complicate your application and create more work for yourself and future deveopers that must maintain your source code. Any programmer working for me that did this would receive a less than satisfactory annual performance review.

Please change your table design so that the columns containing the dates are declared as DATE data type.
   
OK, i do really understand that you can't change the design of tables...

Have a look at example how to convert VARCHAR values into DATETIME data type:
SQL
DECLARE @tmp TABLE (ShowName VARCHAR(30), FDate VARCHAR(10), ToDate VARCHAR(10), IsCurrentYear BIT)

INSERT INTO @tmp (ShowName, FDate, ToDate, IsCurrentYear)
SELECT 'SpiderMan' AS ShowName, '04-01' AS FDate, '09-30' AS ToDate, 0 AS IsCurrentYear
UNION ALL SELECT 'Ice Man', '10-02', '05-30', 1
UNION ALL SELECT 'Gummi Bear', '10-03', '06-30', 1
UNION ALL SELECT 'Killer', '10-04', '07-30', 1
UNION ALL SELECT 'Man in Fire', '10-05', '04-30', 0
UNION ALL SELECT 'Stupid Man', '10-01', '04-30', 1
UNION ALL SELECT 'Family Man', '10-01', '04-30', 1


SELECT ShowName, CONVERT(DATETIME,FDate + '-' + CONVERT(VARCHAR(10),YEAR(GETDATE()))) AS FDate, CONVERT(DATETIME,ToDate + '-' + CONVERT(VARCHAR(10),YEAR(GETDATE()))) AS ToDate
FROM @tmp
WHERE IsCurrentYear=1
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900