Date format issue SQL7 3

[MasterPO]

Hello All:

Yes, another date format problem! I have read all the FAQ's and postings and followed all the links; I have looked in Books on Line and STILL can't solve this problem!

In a Select statement, I simply need to return a date incremented to be the 10th of the following month.

The date value is stored in the database as an integer in the format yyyymmdd. I have found the following successfully converts this to a date format:

Convert(Datetime, Convert(varchar,(ar_due_dt))

I then use Dateadd to increment the month:

Dateadd, mm,1(Convert(Datetime, Convert(varchar,(ar_due_dt)))

My attempted solution was to convert this to a varchar so I could parse the date into the format yyyymm, then concatenate '10' to get my final result back into the format yyyymmdd. However, the varchar conversion returns 'Jan' for the month.

It just seems like there MUST be an easier way to do this!

Help!

 

[skuhlman]

This should do the trick:

convert(datetime,left(convert(char(8),MyField),4)+'-'+substring(convert(char(8),MyField),5,2)+'-10')

 

[MasterPO]

Thanks for the quick response!

The code successfully parses and appends '10 to the current date, but does not increment the month to the following month. Adding 1 to the month value won't work because it may cross over to a new year!

 

[skuhlman]

sorry, i forgot that part when I was doing it. just surround that entire convert.... with:

dateadd(month,1,......)

 

[NickISD]

Not 100% sure what you are trying to return but try this. You can substitute the GetDate() for a date field within a table. Also I would not use int's to store dates use an Sql server datetime one instead.

Hope this helps,

Regards, Nick

SELECT '10' AS TheDay,DatePart(m,Dateadd(mm,1,GetDate())) AS TheMonth,DatePart(yy,Dateadd(mm,1,GetDate())) AS TheYear

 

[tlbroadbent]

Here is another solution. It works across year boundaries.

Select dateadd(m,1,dateadd(d,10-day('20021225'), '20021225'))

dateadd(m,1,dateadd(d,10-day(getdate()), getdate()))

Select dateadd(m,1,dateadd(d,10-day(DateCol), DateCol)) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[MasterPO]

Thanks to all responders!

skuhlman -
The process works to generate the 10th of the next month, but I was not able to convert the datetime back into the format yyyymmdd as an integer. So, I added to your suggestion to get it to work (see below)

NickISD -
I wouldn't use an Int either! Unfortunately, I have inherited this database, so I have to deal with it!

tbroadbent -
When I attempted to replace your '20021225' with the database field, SQL was not able to make the implicit conversion from the integer to the datetime (arithmetic overflow error).

So, taking bits of code from all of you, I made it work by using the following painful process(!) posted here for anyone who may need to do something similar:


Select doc_dt, Left(Convert(char(4),Datepart(yyyy,dateadd(mm,1,convert(datetime,convert(char,aropnfil_sql.doc_dt))))) +
Case when Datepart(mm,dateadd(mm,1,convert(datetime,convert(char(8),aropnfil_sql.doc_dt)))) < 10 then
'0' + Convert(char,Datepart(mm,dateadd(mm,1,convert(datetime,convert(char(8),aropnfil_sql.doc_dt)))))
else Convert(char,Datepart(mm,dateadd(mm,1,convert(datetime,convert(char(8),aropnfil_sql.doc_dt))))) end ,6)
+ '10' as DueDate

Thanks to all -

 

[tlbroadbent]

MasterPO,

You can convert the integer to character, even in the code I provided. And it is still much simpler code.

Select dateadd(m,1,dateadd(d,10-day(Convert(char(8),IntCol)),Convert(char(8),IntCol)))
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[MasterPO]

Terry:

I may be just thick as a brick, but I am not getting the result I need when I ytry your code.

I start out with an integer date like 20030118. I need to increment this to the 10th of the next month (in the same format) so I am looking for 20030210.

When I run your code (above) as is, it returns the correct incremented date in a datetime value like 2003-02-10 00:00:00.

If I convert this to a Char, the Month portion becomes alpha and looks like Feb 10 2003

No matter what I've tried, the solution I posted is the only one I could get to work, though I like the simplicity of your incremnt solution better than mine!

 

[MeanGreen]

MasterPO,

Here is an addition to Terry's code to get you what you want:

Select convert(varchar(8),dateadd(m,1,dateadd(d,10-day(Convert(char(8),@IntCol)),Convert(char(8),@IntCol))),112)

Hope this helps.

 

[MeanGreen]

Actually, I need to take it one step further to make it an integer. Here is the final code:

Select convert(integer,convert(char(8),dateadd(m,1,dateadd(d,10-day(Convert(char(8),@IntCol)),Convert(char(8),@IntCol))),112))

Hope this helps.

 

[MasterPO]

Hallelujah! It works! Thank you both for all the effort to get me this solution.