Date functions :( 1

[elmo29]

I need to calculate the next invoice date when inputting stuff to my table. The next invoice date is always the first of the month, can anyone tell me how i go about this? So far I have

DATEADD(month, 1, GETDATE())

Which gives me the next month. But how do I set the day to be the first of the month?

DATEADD, DIFF, NAME, PART don't really seem to give me what I want.

Please help [sig][/sig]

 

[jnicho02]

try this on the pubs database...

[tt]select ord_date,
[tab]dateadd(month,1,
[tab][tab]convert(datetime,(
[tab][tab][tab]convert(varchar(4),datepart(year,ord_date))+"/" +
[tab][tab][tab]convert(varchar(2),datepart(month,ord_date))+"/01" ))
[tab])
from sales[/tt]

What I did was create a string with the year, month + "/01" to give the first of the month. Converted it to a date, then added a month. This makes sure that a date in December returns January of the year after.

You might need to change the order of the statement depending on how your date format is set, or even generate the month as a name to be sure. Mine is yyyy/mm/dd. [sig]<p> <br><a href=mailto: > </a><br><a href=

http://www.geocities.com/jnicho02/>my

home</a><br> [/sig]

 

[elmo29]

Wow, thanks - wonder why SQL Server makes that sort of thing so difficult? [sig][/sig]