Date format in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date format in SQL

Author

Topic

alanhuro
Starting Member

34 Posts

Posted - 2008-07-22 : 15:20:34

Hi.

I don't know how to write a query to return a date has a format of 'yyyy-mm'. If the day is less than 10 then it should have zero in front of the month.

In Oracle I just use function TO_CHAR(DATETIME, 'YYYY-MM'). Is there any equipvalent function in SQL does the same thing?

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-22 : 15:36:32

SQL Server is not a presentation tool; it is a database server. Whatever client application you are using to interface with your SQL database should format the results. You should never try to "format" data by converting everything to strings; just return raw data and let your client's do their job.

- Jeff
http://weblogs.sqlteam.com/JeffS

alanhuro
Starting Member

34 Posts

Posted - 2008-07-22 : 16:01:11

thank Jeff

But if that the case How can I write a query to get the total value for the same month. For example

Year-Month__________________Sum(ValueA)
2008-01_____________________10
2008-02_____________________30
2008-03_____________________20
etc..

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-22 : 16:06:28

group by datepart(year,DateColumn),datepart(month,DateColumn)

Em

alanhuro
Starting Member

34 Posts

Posted - 2008-07-22 : 16:15:42

The problem is I need to corellate this table with the Oracle table so It need to have the same format. I came up with this but it very lengthly I don't know if any other way to shorten it

select (test.Year + '-' + test.Month), (sum(test.Reheat_gas) + sum(test.Preheat_gas))
from (select cast(year(datetime) as varchar) as Year , CASE
WHEN month(datetime) < 9 THEN '0' + cast(month(datetime) as varchar)
ELSE cast(month(datetime) as varchar)
END as Month, Reheat_gas, Preheat_gas
from rm_gas
where datetime between '2008-01-01' and CURRENT_TIMESTAMP) test
group by (test.Year + '-' + test.Month)
order by (test.Year + '-' + test.Month) asc

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-22 : 16:16:08

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources