Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Date from a string

Posted on 2016-09-12
4
Medium Priority
?
92 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 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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

824 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