I want to select months from Jan to Dec using SQL query

60 pts.
Tags:
SQL development
SQL queries
I want to select months from January to December using SQL query. I will use this for list item note:- without using PL/SQL and less union.

Answer Wiki

Thanks. We'll let you know when a new response is added.

I think Cyberloco’s answer meets the requirements. Deleting my suggested approach.

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kccrosser
    Some additional information on your data structure and requirements would help. Assuming your data is in a table with a date and a value, e.g.: table myTable ( theDate date, theValue integer); and you want to generate a result set with the sum of the values that fall within the given months, you could use: select jandata.total "Jan", febdata.total "Feb", mardata.total "Mar", aprdata.total "Apr", ... from (select sum(theValue) total from myTable where theDate >= '01/01/2009' and theDate < '02/01/2009') jandata, (select sum(theValue) total from myTable where theDate >= '02/01/2009' and theDate < '03/01/2009') febdata, (select sum(theValue) total from myTable where theDate >= '03/01/2009' and theDate < '04/01/2009') mardata, ... This is brute force, but doesn't require any PL/SQL or unions. (Above is in Transact-SQL, in Oracle you would need to cast the dates using the "to_date" function.)
    3,830 pointsBadges:
    report
  • carlosdl
    I guess he wants to select the month names to populate a list box. january february march ...
    69,045 pointsBadges:
    report
  • Kccrosser
    Bizarre - why no union, which would make that trivial? With those constraints, I think you are correct that the only solution is a table of month names.
    3,830 pointsBadges:
    report
  • MEHRA
    From database table you access months in Data Table object by using query
    " SELECT * FROM  tableMonth"
    
    then catch in DataTable object of  ADO.net
    
    
    public DataTable SelectPeron(int Pid) { DataTable tab = new DataTable(); string Con = @"Data Source=sureit44;Initial Catalog=MydataBase;Persist Security Info=True;User ID=sitdev;Password=sitdev1"; using (SqlConnection con = new SqlConnection(Con)) { string query="Select * FROM tableMonth"; SqlCommand cmd = new SqlCommand(query, con); con.Open(); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) da.Fill(tab); con.Close(); } return tab; After that Bind the tab object With DropDownList
    ddl.DataSource=tab;
    ddl.DataBind();
    
    I think this will help you  and  u will get  your  d -zire result
    Thanks
    145 pointsBadges:
    report
  • msi77
    > without using pl/sql t-sql >and less union one union ^-))
    with mth as(
    select 1 as mnum
    union all 
    select 1 + mnum from mth where mnum<12)
    select mnum, datename(month,DATEADD(month,mnum-1,'010101'))  from mth;
    1,660 pointsBadges:
    report
  • cyberloco
    Hi guys !! =D I think I can finally can collaborate in a good manner =D. Check out this one: SELECT TOP 12 DATENAME(MONTH, DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY object_id) - 1,0)) FROM sys.columns Do you like it?
    40 pointsBadges:
    report
  • cyberloco
    I just realized that it has similatiry with the one provided by Mr. Msi777.
    40 pointsBadges:
    report
  • carlosdl
    Good job, Cyberloco ;-)
    69,045 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following