Solved

SQL Date from a string

Posted on 2016-09-12
4
63 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
  • 2
4 Comments
 
LVL 39

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

815 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now