Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 105
  • Last Modified:

SQL Date from a string

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
PhilippeRenaud
Asked:
PhilippeRenaud
  • 2
1 Solution
 
lcohanDatabase AnalystCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
PhilippeRenaudAuthor Commented:
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
 
PhilippeRenaudAuthor Commented:
Scott, i will try your code tonight, thanks alot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now