mid function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

mid function

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-06-02 : 12:27:37

I have an integer field inv_dt that is in YYYYMMDD format.

I'm trying to just grab the month from this field. Substring doesn't seem to work.

I can't seem to find a function to change inv_dt to a string.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-06-02 : 12:46:53

Dates are best held as dates.


DECLARE @inv_dt int
SET @inv_dt = 20100602

SELECT SUBSTRING(CAST(@inv_dt AS char(8)), 5, 2)
	,MONTH(CAST(CAST(@inv_dt AS char(8)) AS datetime))

Kristen
Test

22859 Posts

Posted - 2010-06-02 : 12:49:40

Assusmign inv_dt is a DATETIME data-type then you need to use CONVERT(varchar(24), inv_dt, nnn) to convert it to a string where "nnn" is a number which defines what format you get - see DOCs for more info, its on the page about CONVERT()

There is also a MONTH(inv_dt) function which may give you what you want? (same thing as DATEPART(Month, inv_dt) which you might prefer ...)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-02 : 12:56:22

This avoids conversion

declare @int int
set @int = 20100430
select @int % (@int/10000 )/100

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-03 : 03:39:42

<<
Assusmign inv_dt is a DATETIME data-type
>>

From OP

"I have an integer field inv_dt "

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-03 : 03:42:54

quote:
Originally posted by jimf

This avoids conversion

declare @int int
set @int = 20100430
select @int % (@int/10000 )/100

Jim

Everyday I learn something that somebody else already knew

For validation, you may need convertion. Otherwise you may end with invalid months

declare @int int
set @int = 20101430
select @int % (@int/10000 )/100

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-06-03 : 05:05:38

quote:
Originally posted by madhivanan

<<
Assusmign inv_dt is a DATETIME data-type
>>

From OP

"I have an integer field inv_dt "

Yeah, I saw that on re-reading it later but decided to leave it on the basis that it could be read as "Assuming inv_dt has become a DATETIME data-type" which would be good advice

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources