Can't pull Month from date 2

[trudye10]

I have a date that is stored as numeric in format yyyymmdd. I am trying to extrapolate the month from it and compare it to the current date. Is there a way to do a substring on a numeric field?

Here is my code:

SELECT  
	DDLOAN,
    DDprod,
	DDOFFR,
	DDBLDT,
	DDMFEE
FROM DMS_CONN.johnstd.AOFILES.DDTFEE AS DFEE 
WHERE  DFEE.[DDPROD] = '3000010000' 
	AND year(Getdate()) = left(ddbldt, 4)
	AND month(Getdate()) = substring(ddbldt, 4, 2)

I tried the CAST statement:
AND month(Getdate()) = substring(cast(ddbldt as char), 4, 2) but got no return records.

Is there another funciton I should be using

Thanx much,
Trudye

 

[drtree]

Hey,

I think that you need to adjust your substring statement - it should be: substring(cast(20041112 as char(8)), 5, 2).
Using '4' as the beginning character got the last digit of the year when I ran it.

HTH,

Doc Tree

 

[Jamfool]

you can always use cast.

select CAST('20071231' AS DATETIME)

 

[trudye10]

Thanks Guys so much for repsonding however, I'm don't understanding how the proposed options will get me the value I want from field ddbldt. I am trying to compare month values in an effort to get a specific year and month.

Trudye

 

[SgtJarrow]

Combing a few of the previous posts with one more cast may work...you can try replacing:

AND month(Getdate()) = substring(ddbldt, 4, 2)

with

AND month(Getdate()) = cast(substring(ddbldt, 5, 2) as Int)

This brings the string you extracted from your date back to an int to compare against the month function which returns an int.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[gmmastros]

So far, the solutions presented are not sargable. This means... if an index exists on the ddbldt column, it will NOT be used. There is a way to write this query such that an index would be used. Anyoen care to give it a shot? If there's not a solution within the next 30 minutes, I will post one. The first person (that already posted a potential solution) that shows a sargable where clause will get a star from me!



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kaht]

with all this converting and date functions, it won't take advantages of any indexes on the column, but it will work:

[COLOR=blue]declare[/color] @a [COLOR=blue]table[/color] (b [COLOR=blue]numeric[/color])

[COLOR=blue]set[/color] [COLOR=#FF00FF]nocount[/color] [COLOR=blue]on[/color]
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]select[/color] 20060101
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]select[/color] 20060901
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]select[/color] 20070101
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]select[/color] 20070901
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]select[/color] 20080101
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]select[/color] 20080901

[COLOR=blue]select[/color] *
[COLOR=blue]from[/color] @a
[COLOR=blue]where[/color] [COLOR=#FF00FF]datepart[/color](mm, [COLOR=#FF00FF]convert[/color]([COLOR=blue]char[/color](10), b)) = [COLOR=#FF00FF]datepart[/color](mm, [COLOR=#FF00FF]getdate[/color]())
   and [COLOR=#FF00FF]datepart[/color](yy, [COLOR=#FF00FF]convert[/color]([COLOR=blue]char[/color](10), b)) = [COLOR=#FF00FF]datepart[/color](yy, [COLOR=#FF00FF]getdate[/color]())

Why did you choose numeric as the datatype for the column? At least in storing it in character form you could use the date functions directly on the column.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[kaht]

fine George, with my limited SQL knowledge this should be sargable (and I even used your formatter for bonus points)

[COLOR=blue]declare[/color] @a [COLOR=blue]table[/color] (b [COLOR=blue]numeric[/color])

[COLOR=blue]set[/color] [COLOR=#FF00FF]nocount[/color] [COLOR=blue]on[/color]
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]select[/color] 20060101
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]select[/color] 20060901
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]select[/color] 20070101
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]select[/color] 20070901
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]select[/color] 20080101
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @a [COLOR=blue]select[/color] 20080901

[COLOR=blue]select[/color] *
[COLOR=blue]from[/color] @a
[COLOR=blue]where[/color] b >= ([COLOR=#FF00FF]datepart[/color](yy, [COLOR=#FF00FF]getdate[/color]()) * 10000) + ([COLOR=#FF00FF]datepart[/color](mm, [COLOR=#FF00FF]getdate[/color]()) * 100) + 1
   and b < ([COLOR=#FF00FF]datepart[/color](yy, [COLOR=#FF00FF]getdate[/color]()) * 10000) + (([COLOR=#FF00FF]datepart[/color](mm, [COLOR=#FF00FF]getdate[/color]()) + 1) * 100) + 1

I also didn't use the OP's code, but they should be able to figure it out using my code.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[kaht]

One thing to point out about my code, is that when the current month is December then the second part of the where clause will return 13 when extracting the month which is obviously not a valid month but since we're comparing it to numeric data it should still return the proper results.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[gmmastros]

Nice job Kaht. You get a star. To fix the 'december' date issue, you should keep your date (as a date) for as long as you can. Like this...

[COLOR=blue]SELECT[/color] DDLOAN,
       DDprod,
       DDOFFR,
       DDBLDT,
       DDMFEE
[COLOR=blue]FROM[/color]   DMS_CONN.johnstd.AOFILES.DDTFEE [COLOR=blue]AS[/color] DFEE
[COLOR=blue]WHERE[/color]  DFEE.[DDPROD] = [COLOR=red]'3000010000'[/color]
       And ddbldt >= [COLOR=#FF00FF]Convert[/color]([COLOR=blue]int[/color], [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](6), [COLOR=#FF00FF]GetDate[/color](), 112) * 100 + 1)
       And ddbldt < [COLOR=#FF00FF]Convert[/color]([COLOR=blue]int[/color], [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](6), [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Month[/color], 1, [COLOR=#FF00FF]GetDate[/color]()), 112) * 100 + 1)

It should be stated, for trudye10's benefit, that this query will likely perform better because it would use an index on that column (if it exists).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kaht]

Thanks for the tip, George.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[trudye10]

Hi Guys thanx for responding so quickly. I make it a practice never to code anything I cannot support. I am new to SQL Server and I have no idea what you just coded.

Although I appreciate the fun you guys had creating this complex formula, there has got to be an easier way to pull the month from the 5th and 6th bytes of an 8 byte numeric field.

Does SQL Server have a function simular to VBA's MID()?

To answer a kaht's question I inherited this file. It is my cross to bear. That is the reason I will need to find a function/forumula I can tweak as needed. I will be working with this date A LOT in the future.

Thanks for everything,
Trudye

 

[kaht]

Instead of trying to use substring functions on numeric data that is not string-like in nature, why not learn and understand the methods posted above? Especially since you are new to SQL Server - no need to start out on the wrong foot. If you are going to be working with the date a lot in the future, it would be wise and extremely beneficial to take advantage of any indexes on that date. Your data retrieval times will be much shorter that way. Not to mention, your boss will love you.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

 

[gmmastros]

>> Does SQL Server have a function simular to VBA's MID()?

Yes. You used it in your first post. Unfortunately, you were slightly off. You started your substring at the 4th character, but should have used the 5th instead.

SELECT  
    DDLOAN,
    DDprod,
    DDOFFR,
    DDBLDT,
    DDMFEE
FROM DMS_CONN.johnstd.AOFILES.DDTFEE AS DFEE
WHERE  DFEE.[DDPROD] = '3000010000'
    AND year(Getdate()) = left(ddbldt, 4)
    AND month(Getdate()) = substring(ddbldt, [!]5[/!], 2)

I applaud your conviction. I make it a practice never to code anything I cannot support. However, since it was pointed out here that your current method is not sargable (meaning that it cannot use an index), I would recommend you learn the methods shown. While the formulas do appear to be complex, they will actually perform better.

If you would like me to explain the formulas, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[trudye10]

George, thank you so very much for your patience, professionalism and your understanding of what it is like to be a newbie to SQL Server.

Also please accept my gratitude for taking your valuable time to explain the posted formula. I am sure I am not the only one who does not understand it. We also have to keep in mind that these posts are referenced over and over. Many times I have pulled up old posts and awarded a star because my question was answered. Hopefully this post will be referenced many times.

I would appreciate any light you can shed on the formula.

Thank again,
Trudye

 

[gmmastros]

When understanding complex formulas like this, it is best to work from the inside out.

[tt][blue]Convert(int, Convert(VarChar(6), [!]GetDate()[/!], 112) * 100 + 1)[/blue][/tt]

GetDate returns the current date and time.

[tt][blue]Convert(int, [!]Convert(VarChar(6), GetDate(), 112)[/!] * 100 + 1)[/blue][/tt]

Since there are many different representations for date and time (how it is displayed), SQL Server has many different built-in ways of converting that to a string. Convert has 3 arguments. The first determines the data type to convert to. In this case, we are using varchar(6). So, we will convert GetDate() to a 6 character string. The 3rd parameter is for style. Style 112 is YYYYMMDD. Since we are only taking the first 6 characters, you end up with YYYYMM.

[tt][blue]Convert(int, [!]Convert(VarChar(6), GetDate(), 112) * 100 + 1[/!])[/blue][/tt]

For today, the convert part returns 200709. We can treat this as a number. By multiplying this number by 100, get 20070900. Then, add 1, we get... 20070901.

The second formula is very similar to the first. The only difference is that we add 1 month to GetDate().

Your original query had a where clause for month and year. If you think about the way you are storing your dates, you essentially have yyyymmdd. So, any date in September 2007 will start with 200709, right? By applying values to the formulas presented, we end up with...

[tt][blue]WHERE DFEE.[DDPROD] = '3000010000'
And ddbldt >= 20070901
And ddbldt < 20071001[/blue][/tt]

This will effectively return any value where the date is in September 2007.

Now, let me explain why this query is better. If you have this column indexed, this query will be able to use the index to return the information faster. Think of an index as a dictionary. If you had a (paper) dictionary in front of you, and you wanted to see all the words that start with ST, that would be pretty easy to do. You would be able to flip to the right page(s) within a couple seconds. Now, imagine you wanted to see a list of words that have ST in the middle of the word. You would have to flip through every page and examine every word in order to do this. That would certainly take more than a couple seconds.

By using a where clause condition for month (the 5th and 6th position of your string), you are effetively looking for values in the middle 'of the word'. Since you also include the year as another part of your where clause, we can change the filter criteria to 'anything that starts with 200709.

Does this make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[trudye10]

That makes PERFECT sense, it is clear as mud (Smile). It seems so easy when someone breaks it down.

I and I'm sure others will apprciate your explanation.

Thanx so very much,
Trudye