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 |
|
jbphoenix
Yak Posting Veteran
68 Posts |
 Posted - 2009-10-15 : 12:02:08
|
| I'm trying to find the end of the month date. My date field looks like this 200909, it only has the year and month. However my requirements are to have the end of the month date in the report. How would I get the end of the month date for any month? 20090930Thanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-10-15 : 12:04:30
|
what is the data type of your date field ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
JCirocco
Constraint Violating Yak Guru
392 Posts |
Posted - 2009-10-15 : 12:38:05
|
| Add one month then subtract 1 day?John |
|
|
|
scott_leseman
Starting Member
5 Posts |
Posted - 2009-10-15 : 12:42:17
|
| Assuming your date field is text with fixed width of 6 characters, something like this should work:SELECT '200909' + CONVERT(CHAR(2), DAY(DATEADD(d,-1,DATEADD(m,1,CONVERT(DATETIME,RIGHT('200909',2) + '/1/' + LEFT('200909',4))))))Replace the string '200909' with the name of your date field.It takes your date and converts it to a datetime of 9/1/2009. Next it adds 1 month to that date and subtracts 1 day from that giving you the last day of the month. Then it takes the day portion of that date and adds it to the end of your current date field.You could also simplify this by creating a user defined function and using variables. |
|
|
|
jbphoenix
Yak Posting Veteran
68 Posts |
Posted - 2009-10-15 : 12:48:30
|
| Thanks everyone for your help - this is what I came up with and it is working. I ended up going with this format mm/dd/yyyyConvert(VARCHAR(10), DateAdd(dd,-1,DateAdd(m,1,convert(varchar(10),mnth)+'01')),101) as DateProcessed |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-10-16 : 04:06:17
|
quote:
Originally posted by jbphoenix
Thanks everyone for your help - this is what I came up with and it is working. I ended up going with this format mm/dd/yyyyConvert(VARCHAR(10), DateAdd(dd,-1,DateAdd(m,1,convert(varchar(10),mnth)+'01')),101) as DateProcessed
You haven't answered to khtan's question  MadhivananFailing to plan is Planning to fail |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
|
|
|
|
|
End of month date for 200909