Solved

SQL Date from a string

Posted on 2016-09-12
4
77 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

752 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