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.
| 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/
|
|
|
|
|
|
|
|
|
Getting the MMM-YY within a specific Date range