SQL Server Performance Forum – Threads Archive

sql server date conversion

Hi All, Is there a way in sql server to combine two columns of a table, month and year and get "date" so that the resultant date can be compared in where clause.[:I] Thanks M N taneja

See if this help:<a target="_blank" href=http://www.sql-server-performance.com/fk_datetime.asp>http://www.sql-server-performance.com/fk_datetime.asp</a> If not, please post more information about what you’re trying to do. An example then would be fine. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Thanks for such prompt reply,
And sorry , i cudnt get any help from the topic referred.
what I want is, that in my table I have month column and year column, But now I want an (earlier) month(/date) less than the current month for which data is there in the table.
eg if data exist for the June 2005, then in the entry form of August, June’s data shud come, which I can further update. M N Taneja

Hi,
Do you want something:
Select monthCol+YearCol as monthyear from table

or try this: declare @table1 table(monthcol varchar(2),yearcol varchar(4))
insert @table1 values(‘8′,’2005’)
insert @table1 values(‘7′,’2005’)
insert @table1 values(‘6′,’2005’)
select monthcol+yearcol reqdate from @table1
where monthcol+yearcol=cast(datepart(m,getdate())as varchar(2))+cast(datepart(yy,getdate())as varchar(4))

Sometimes some data say more than words. I’m guessing now.
SELECT * FROM yourtable WHERE yearcol*10000+monthcol*100+1<‘20050801’ If this still won’t help, please read this linkhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9544 and provide the information mentioned there.
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

>>Is there a way in sql server to combine two columns of a table, month and year and get "date" Cant you use this? Select columns from yourtable
where month(datecol)=monthvalue and year(datecol)=yearvalue
Madhivanan Failing to plan is Planning to fail

quote:Originally posted by mntaneja Hi All, Is there a way in sql server to combine two columns of a table, month and year and get "date" so that the resultant date can be compared in where clause.[:I] Thanks M N taneja

The big question here is – so that the resultant date can be compared in where clause – … well, can be compared to WHAT?

Thank u all, -Frank – I tried urs one as
"SELECT max(month_code) FROM storagecap WHERE [year]*10000+month_code*100+1<‘20051001’" but I m afraid to say that its not giving the desired or say right result. well here my client asks me to fill the entry screen with the previous available data lying in table so that he just make few changes in the data and insert it as new set of data for the current month, eg if data for say June 2005 is there,and i have to input data for Aug 2005, then data for month June 2005 shud come and not June 2004.Structure of table is
-MonthCode
-Year
-Amount -Adriaan ,Comapre it with date
-Madhivanan, there is month column and year column and not date column
– ranjitjain, no, its giving simple sum of two
M N Taneja

I meant Select columns from yourtable
where month(‘20050601’)=monthvalue and year(‘20050601’)=yearvalue Madhivanan Failing to plan is Planning to fail

Okay, so you basically need the most recent month for which there is data in your table (but not the current month). Assuming your MonthCode and Year columns are numeric, this is the query: SELECT TOP 1 t1.*
FROM MyTable t1
WHERE ((t1.Year * 100) + t1.MonthCode) < ((YEAR(GETDATE()) * 100) + MONTH(GETDATE()))
ORDER BY t1.Year DESC, t1.MonthCode DESC

Why are you insistently refusing to give some sample data and the required output? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] You say you want June data to be inserted as August data. What about July? Can we assume that it always the penultimate month or as Adriaan said should that be the most recent month for which data exists? Next you say you want to compare with date. I see no DATETIME column in your table structure. I’m sure, your problem could have been solved hours ago if only you had provided the necessary information. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Madhivanan, first I have to find the "last" month , here in this case "June 2005"

Post some sample data with expected result
That would be really helpful
Madhivanan Failing to plan is Planning to fail

say I have entry screen with month , year and amount1, amount2, amount3……amount100.
Now user wants that entry screen shud come up with last available data. My query is to find that "last month" for which data is there in the table.

Not sure whether this is correct until you post some sample data
Select t1.* from yourtable t1 inner
join (
select Top 1 max(monthcode) as mon,yearcode from yourtable group by yearcode
order by yearcode desc ) T2
on t1.monthcode =t2.mon and t1.yearcode =t2.yearcode
Madhivanan Failing to plan is Planning to fail

Hi All, [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] I dont know how to upload my sample data.Let me try this way. The table design: ST_CODE int41 WareHousesmallint 21 Month_Codesmallint21 [Year]smallint21 NoOfWHS float81 CovOwn float81 CovHirfloat81 CapOwn float81 CapHirfloat81 PerUtilfloat81 (yes Frank there is no datetime column, otherwise I had simply compared the date with the current date) Now say Data is there for the month 5,2004 8,2004 10,2004 12,2004 1,2005 5,2005 And now the want to user to insert data for June 2005(although the month these days is Aug 2005), and he want that data of month May2005(5,2005) shud appear in entry screen which he can update and later we insert it as new record set for June 2005.So My query is to find the max month in table but less than the user inputed month (i.e June in this case) M N taneja

Try this Select t1.* from yourtable t1 inner
join (
select Top 1 max(monthcode) as mon,yearcode from yourtable
where monthcode<yourmonthcode group by yearcode
order by yearcode desc ) T2
on t1.monthcode =t2.mon and t1.yearcode =t2.yearcode
Madhivanan Failing to plan is Planning to fail

Madhivanan
what about the year!!!
It shud be month+Year I mean for June and 2005 it shud be "June,2005"
and month and year cannot be compared separately and care also needed for month of January where month december(12) wud be greater than January.

Then use where monthcode<yourmonthcode and yearcode=youryearcode in the above query
Madhivanan Failing to plan is Planning to fail

quote:Originally posted by mntaneja Madhivanan
what about the year!!!
It shud be month+Year I mean for June and 2005 it shud be "June,2005"
and month and year cannot be compared separately and care also needed for month of January where month december(12) wud be greater than January.

mntaneja, Seems like you did not even bother to test my query, which does take into account both the year and the month.[xx(] Here we go again: SELECT TOP 1 t1.*
FROM MyTable t1
WHERE ((t1.Year * 100) + t1.MonthCode) < ((YEAR(GETDATE()) * 100) + MONTH(GETDATE()))
ORDER BY t1.Year DESC, t1.MonthCode DESC

No Adriaan, I have tried ur query as well but sorry

What about my query? You are not giving proper reply whenever you reply If that query doesnot work, you should give the reason
Did you get any error or did you get no results?
Madhivanan Failing to plan is Planning to fail

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by mntaneja No Adriaan, I have tried ur query as well but sorry<hr height="1" noshade id="quote"></blockquote id="quote"> If you want us to help you then you must be clear about exactly what is wrong with the results from the solutions we’re offering.[<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />!][<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />][xx(] Give us some data that is in your table (all relevant columns) then tell us which part of that data you want returned by the query.

Adiraan â€œ
SELECT TOP 1 t1.*
FROM MyTable t1
WHERE ((t1.Year * 100) + t1.MonthCode) < ((YEAR(GETDATE()) * 100) + MONTH(GETDATE()))
ORDER BY t1.Year DESC, t1.MonthCode DESCâ€ In this query we are not able to compare it with the user given month and year Madhivanan â€œwhere monthcode<yourmonthcode and yearcode=youryearcodeâ€ ——— Say my user ask for January in that case year which wud be dec of last year wud not be equal to userYear,eg he want jto input data for jan 2005 then I will need Dec 2004 data .

quote:Originally posted by mntaneja Adiraan â€œ
SELECT TOP 1 t1.*
FROM MyTable t1
WHERE ((t1.Year * 100) + t1.MonthCode) < ((YEAR(GETDATE()) * 100) + MONTH(GETDATE()))
ORDER BY t1.Year DESC, t1.MonthCode DESCâ€ In this query we are not able to compare it with the user given month and year Madhivanan â€œwhere monthcode<yourmonthcode and yearcode=youryearcodeâ€ ——— Say my user ask for January in that case year which wud be dec of last year wud not be equal to userYear,eg he want jto input data for jan 2005 then I will need Dec 2004 data .

Okay, now I see your point. Here’s a simple stored procedure that would do that for you:
CREATE PROCEDURE dbo.MyProc
(@Month SMALLINT, @Year SMALLINT)
SELECT TOP 1 t1.*
FROM MyTable t1
WHERE ((t1.Year * 100) + t1.MonthCode) < ((@Year * 100) + @Month))
ORDER BY t1.Year DESC, t1.MonthCode DESC

yes Adriaan its working Thanks a lot [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] And does anyone know any site where i can find little advanced sample sql queries to be solved to improve my tsql.

]]>