designing an inventory system and i want to generate crystal reports from sql queries. Actually want to generate monthly and yearly reports from the daily sales reports i have populated. someone please help me.

In what format dates are being stored ?


SQL Server supports DatePart so if you are actually storing your dates as date type values then:

SELECT DatePart(month, Mytable.MyDateValue) As TheMonth FROM MyTable

Also I've fairly sure I've seen MONTH() used as well:

SELECT MONTH(MyDate) As TheMonth From MyTable

Both of these will give you an integer e.g. 1 for January etc...
You could then use MonthName in your to get the actual name...

Note of caution, if you are inputing the date values you should always input them in dd mmmm yyyy or yyyy-mm-dd format this ensures the dates are being stored in the correct format in your DB. i.e. 08/09/2011 to me is the 8th Septemeber (European format) but could be August 9th to someone from the US or server using US formats.

thank you very much....but i also want to generate monthly and yearly reports based on daily transaction. How do i go about it.
I have Salestbl with fields recID, ItemName, Qty, UnitPrice, TotalPrice and DateOfEntry.
i want to create monthly bar charts when user enter year

Do you want any summary information as well ?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.