String to Date Conversion

Question

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 9-Jul-13 3:09am

krishna_goluguri

Updated 9-Jul-13 3:25am

Maciej Los

v3

Add a Solution

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mike Meinz · Answer 1 · 2013-07-09T04:52:00

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.

Maciej Los · Answer 2 · 2013-07-09T09:10:00

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

String to Date Conversion

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0