date in sql

Question

Post reply

date in sql

cpinky01

Ten Centuries

Points: 1084
More actions
September 21, 2010 at 7:30 am

#229600

hi ,
i need to have the current 'thisyearmonth' expressed as 201008
means august 2010 has to be expressed as 201008
like this get the current year and express yearmonth as 201008
if month is sep of 2010 then 201009
can any one help me with this
thank you

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

fhanlon SSChampion Points: 13131 More actions · Answer 1

do a search on this site for date formats and you find lots of entries that may help like:

http://qa.sqlservercentral.com/scripts/Datetime+Manipulation/61393/

or in other sites like:

http://sqlserverplanet.com/sql/cast-date-with-no-time/

You may want to try something like:

SELECT CONVERT(varchar(6),GETDATE(), 112)

Francis

Philipp Post Default port Points: 1401 More actions · Answer 2

If I understand correctly, you will need a calendar table which you can join to any DATE or DATETIME column and return the year_month portion accordingly

CREATE TABLE Calendar

(calendar_date DATE NOT NULL PRIMARY KEY,

year_month CHAR(6) NOT NULL);

A similar way is the Report_Periods table which looks like this

CREATE TABLE Report_Periods

(period_name CHAR(6) NOT NULL PRIMARY KEY,

period_start_date DATE NOT NULL,

period_end_date DATE NOT NULL);

You JOIN it like this: "... WHERE your_date BETWEEN period_start_date AND period_end_date" in order to get the period_name which could be yearmonth as you need.

brgds

Philipp Post

cpinky01 Ten Centuries Points: 1084 More actions · Answer 3

THANKS FOR REPLYING ME

I DID LIKE THIS

select CAST(YEAR(GETDATE()) AS varchar(30))+

CASE WHEN Month(GETDATE()) >9 THEN '' ELSE '0' END

+CAST(Month(GETDATE()) AS varchar(30))

THIS DISPLAYS 201009

I NEED TO GET LAST TWO YEARMONTH FROM CURRENTYEARMONTH THAT IS 201008 AND 201007

HOW CAN I DO THIS.

CAN I PUT IN A VARIABLE AND DECREMENT IT?

THANK YOU

Philipp Post Default port Points: 1401 More actions · Answer 4

DATEADD(month, -1, CURRENT_TIMESTAMP) resp DATEADD(month, -2, CURRENT_TIMESTAMP) and then convert back to your year_month format.

brgds

Philipp Post