Getting the MMM-YY within a specific Date range

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

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Getting the MMM-YY within a specific Date range

Author

Topic

JerretF
Starting Member

4 Posts

Posted - 2011-09-06 : 17:26:26

How would one get the MON-YR within specific date range?

I'm trying to make it so if a bill was issued between the 16th of the first month and the 15th second month it is counted as the a bill from the first month in MMM-YY format.
For example:
01-16-2011 - 02-15-2011 = Jan-11
02-16-2011 - 03-15-2011 = Feb-11
03-16-2011 - 04-15-2011 = Mar-11

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 23:03:20

you mean this?

SELECT STUFF(RIGHT(CONVERT(varchar(20),DATEADD(dd,-15,datefield),6),6),4,1,'-')  FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JerretF
Starting Member

4 Posts

Posted - 2011-09-08 : 13:20:06

Visakh,
This works great! Thank you! However how can I change it to mm/dd/yyyy format?
Would like it to show the month and the first day of the month followed by the year.
For example:
01-16-2011 - 02-15-2011 = 1/1/2011

quote:
Originally posted by visakh16

you mean this?

SELECT STUFF(RIGHT(CONVERT(varchar(20),DATEADD(dd,-15,datefield),6),6),4,1,'-') FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 08:26:08

this should be done in your presentation layer if possible as you can very easily achieve this using format functions there.

however, if you've no way to do it there, use convert in t-sql like

SELECT CONVERT(varchar(11),DATEADD(dd,-15,datefield),101)  FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JerretF
Starting Member

4 Posts

Posted - 2011-09-09 : 11:14:54

Figured it out. This is how you do it:

select Stuff(CONVERT(VARCHAR(10), DATEADD(dd, -15,datefield), 101),4,2,'1') from table

quote:
Originally posted by visakh16

this should be done in your presentation layer if possible as you can very easily achieve this using format functions there.

however, if you've no way to do it there, use convert in t-sql like

SELECT CONVERT(varchar(11),DATEADD(dd,-15,datefield),101) FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-10 : 01:24:13

quote:
Originally posted by JerretF

Figured it out. This is how you do it:

select Stuff(CONVERT(VARCHAR(10), DATEADD(dd, -15,datefield), 101),4,2,'1') from table

quote:
Originally posted by visakh16

this should be done in your presentation layer if possible as you can very easily achieve this using format functions there.

however, if you've no way to do it there, use convert in t-sql like

SELECT CONVERT(varchar(11),DATEADD(dd,-15,datefield),101) FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

whats purpose of stuffing that 1?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources