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 |
|
BadBoy House
Starting Member
41 Posts |
 Posted - 2010-06-16 : 09:44:34
|
| I need to find out what the formula is to add 9 months and 1 day to a database date field.For reference, the field name is tblClientExtraDetails."A/C_Yr_End_09"I've got the formula all working ok in Crystal Reports (see below) but the SQL query formula has got me stuck!CDate (DateAdd ('m' , 9 , {tblClientExtraDetails.A/C_Yr_End_09})) + 1Any ideas? |
|
|
maxald
Starting Member
7 Posts |
Posted - 2010-06-16 : 09:48:49
|
| DATEADD(DAY, 1, DATEADD(MONTH, 9, YourDate)) |
 |
|
|
BadBoy House
Starting Member
41 Posts |
Posted - 2010-06-16 : 10:13:29
|
| Fantastic. Thanks very much!Typical the SQL format isn't that different to Crystal!cheers |
|
|
|
BadBoy House
Starting Member
41 Posts |
Posted - 2010-06-16 : 10:25:58
|
| What about this one. I need this as well in the query:Take the current date (e.g. 16/06/10) and set it to display 01/06/10 - i.e. take the month and year but set the day to 1.Is this do-able like it is in Crystal? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-06-16 : 10:28:55
|
dateadd(month, datediff(month, 0, YourDate), 0)
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-06-16 : 10:30:44
|
| DATEADD(MONTH,DATEDIFF(MONTH, 0, YourDate),0)MadhivananFailing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-06-16 : 10:31:58
|
 MadhivananFailing to plan is Planning to fail |
|
|
|
BadBoy House
Starting Member
41 Posts |
Posted - 2010-06-16 : 10:33:24
|
| Once again thanks very much!! |
|
|
|
|
|
|
|
|
How do you add 9 months and 1 day to a date field?