Date Part - 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 Part

Author

Topic

JBee
Starting Member

9 Posts

Posted - 2007-09-03 : 06:34:14

Hi,

i am trying to write a query the pulls out a table which has date information stored as dd/mm/yyy but all i want is the month and the year.

So i tried

select datepart(mm, dtinsertdate)
from incident

which only gives me month and also used

select datepart(year,dtinsertdate) [year], datepart(month, dtinsertdate)[month],

Which works but i want both in the same column.

But when trying to get both mm, yyyy it looks like it only supports one datepart. Is there any way aroung this?

Many Thanks

John

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 06:40:14

Why would you like this? Can't this be formatted in your user application?

DATENAME(YEAR, Col1) + DATENAME(MONTH, Col1)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 06:41:10

100 * DATEPART(YEAR, Col1) + DATEPART(MONTH, Col1)

Also read Books Online (the Microsoft SQL Server help file) for more information about proper parameters.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-03 : 06:59:57

What is the datatype of date column?

1 Always use proper datatype DATETIME to store dates
2 Let your front end format it
3 Create new column with proper DATETIME datatype and update it from other column and use new column for your queries

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-03 : 07:50:52

You could "round" the datetime value to the 1st of the month

e.g.

SELECT DATEADD(Month, DATEDIFF(Month, 0, GetDate()), 0)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-03 : 08:10:20

quote:
Originally posted by Kristen

You could "round" the datetime value to the 1st of the month

e.g.

SELECT DATEADD(Month, DATEDIFF(Month, 0, GetDate()), 0)

Kristen

Provided proper datetime datatype is used

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-03 : 08:27:33

"Provided proper datetime datatype is used"

Indeed, but looks like JBee already has that

quote:
select datepart(mm, dtinsertdate)
from incident

Kristen

JBee
Starting Member

9 Posts

Posted - 2007-09-03 : 08:56:15

Thank you all.

I have used Kristens solution, but yes the Excel spreadhseet could easily have a macro added to take care of the month and year and concatenate the two columns. Just wondered if i could get round it.

Many Thanks

John

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 10:56:58

If using Excel, you could easily do this with only formatting in Excel!

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources