Solved

SQL Date from a string

Posted on 2016-09-12
4
83 Views
Last Modified: 2016-09-13
Hello EE, I have a column field in a table called "Instruction Text"

inside, users are puting text and a date such as :

DATE JOB FOR OCTOBER 20 BlaBlaBla
Date job for November 18 BlaBlaBla


I know its not the right way to do things, dont worry, but until this get fix, I need a way to be able to search this field and do a where clause with BETWEEN Dates to search and get results.

for example WHERE (do stuffhere) >= date1    

I know we could do a LIKE and get date as a string but I need the user to be able to search either FROM DATE  OR  TO DATE

in other words, the user could enter only a from date and not a TO Date (so we cannot use Between)
or vice versa....


can you help me?

i know what you are going to say, if text is like this " OCTOBER20"  without space it will not work, well thats the user problem. they have a shitty system its not our problem.
0
Comment
Question by:PhilippeRenaud
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 41795089
Sorry to say things are a bit confusing as you say "I need a way to be able to search this field and do a where clause with BETWEEN Dates to search and get results." but then in brackets - " (so we cannot use Between)'" - so are you allowed to use between or not?

And what about the Year? as from "OCTOBER 20" to "November 18" could be a lot of data for many years if the system was operational for long time that is not even sure you want it deleted right?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41795112
I've used CROSS APPLYs to simplify the SELECT columns.  I assume you're looking for current date forward, but that part can be added/adjusted later if the part below works OK for you:


SELECT
    [instruction Text],
    CASE WHEN month_b_start < month_a_start
        THEN SUBSTRING([instruction Text], month_b_start, month_b_len + 3)
        ELSE SUBSTRING([instruction Text], month_a_start, month_a_len + 3)
        END AS start_date,
    CASE WHEN month_b_start < month_a_start
        THEN SUBSTRING([instruction Text], month_a_start, month_a_len + 3)
        ELSE SUBSTRING([instruction Text], month_b_start, month_b_len + 3)
        END AS end_date        
FROM (
    VALUES('DATE JOB FOR OCTOBER 20 BlaBlaBla'),('Date job for November 18 BlaBlaBla'),
          ('DATE JOB FOR December 1 thru January 23')
) AS test_data([instruction Text])
CROSS APPLY (
    SELECT COALESCE(
         70000 + NULLIF(CHARINDEX('January', [instruction Text]), 0),
         80000 + NULLIF(CHARINDEX('February', [instruction Text]), 0),
         50000 + NULLIF(CHARINDEX('March', [instruction Text]), 0),
         50000 + NULLIF(CHARINDEX('April', [instruction Text]), 0),
         30000 + NULLIF(CHARINDEX('May', [instruction Text]), 0),
         40000 + NULLIF(CHARINDEX('June', [instruction Text]), 0),
         40000 + NULLIF(CHARINDEX('July', [instruction Text]), 0),
         60000 + NULLIF(CHARINDEX('August', [instruction Text]), 0),
         90000 + NULLIF(CHARINDEX('September', [instruction Text]), 0),
         70000 + NULLIF(CHARINDEX('October', [instruction Text]), 0),
         80000 + NULLIF(CHARINDEX('November', [instruction Text]), 0),
         80000 + NULLIF(CHARINDEX('December', [instruction Text]), 0)
        ) AS month_len_and_start_a
) AS ca1
CROSS APPLY (
    SELECT STUFF([instruction Text], month_len_and_start_a % 10000, 3, '') AS instruction_text2
) AS ca2
CROSS APPLY (
    SELECT COALESCE(
         70000 + NULLIF(CHARINDEX('January', instruction_text2), 0),
         80000 + NULLIF(CHARINDEX('February', instruction_text2), 0),
         50000 + NULLIF(CHARINDEX('March', instruction_text2), 0),
         50000 + NULLIF(CHARINDEX('April', instruction_text2), 0),
         30000 + NULLIF(CHARINDEX('May', instruction_text2), 0),
         40000 + NULLIF(CHARINDEX('June', instruction_text2), 0),
         40000 + NULLIF(CHARINDEX('July', instruction_text2), 0),
         60000 + NULLIF(CHARINDEX('August', instruction_text2), 0),
         90000 + NULLIF(CHARINDEX('September', instruction_text2), 0),
         70000 + NULLIF(CHARINDEX('October', instruction_text2), 0),
         80000 + NULLIF(CHARINDEX('November', instruction_text2), 0),
         80000 + NULLIF(CHARINDEX('December', instruction_text2), 0)
        ) AS month_len_and_start_b
) AS ca3
CROSS APPLY (
    SELECT month_len_and_start_a / 10000 AS month_a_len,
        month_len_and_start_a % 10000 AS month_a_start,
        month_len_and_start_b / 10000 AS month_b_len,
        month_len_and_start_b % 10000 AS month_b_start
) AS ca4
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 41795114
Icohan, sorry,

i can use between, its just that user wont be able to search only with a "from" but its not the end of the world
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 41795116
Scott, i will try your code tonight, thanks alot
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question