DateTime Menace

I have one table where I load the dates using datetime datatype. Now I need to copy only the month and year from tht and put it in another table as varchar, what will be the best way to strip the data........any exact query written will be great.

Code Snippet

SELECT SUBSTRING(CONVERT(VARCHAR(10), date, 103),4,7) FROM TABLENAME

Thanks,

Loonysan

http://mystutter.blogspot.com

|||An excellent suggestion from Loonysan.

Also, note that you can get it in the format mm/yy by just changing the final value in the CONVERT function to 3 as below:

Code Snippet

SELECT SUBSTRING(CONVERT(VARCHAR(10), date, 3),4,7)

If you want to put the month in one column and the year in another column you'll need to look at DATEPART

Code Snippet

SELECT DATEPART(Month, date), DATEPART(Year, date)

HTH!

|||

hey thanks guys.....the suggestions are really awesome, i will try al the three queries and will see which one is best for the my db.

|||

Hi,Chintan

here come another, just for you to ref. :=)

Select Convert(char(6),getdate(),112)

go

the result as below.

200708

(1 rows affected)

try it.

Best Regrads,

Hunt

|||

create procedure inmarine_Pre

as

truncate table herm_inmar_pre

insert into herm_inmar_pre

(

accountingdate,

transactioneffectivedate,

transactionexpirationdate,

statecode,

sublinecode,

classificationcode,

zipcode

)

select p.entrydate,

p.premiumeffectivedate,

p.policyexpirationdate,

p.statecode,

s.sublinecode,

s.classcode,

p.postalcode

from hermitage.dbo.premiumdirect as p join hermitage.dbo.premiumstatdirect as s

on p.invoiceno = s.invoiceno and

s.lineofbusinesscode = '090'

and p.entrydate between '01/01/2007' and '12/31/2007'

order by p.entrydate asc

go

This is the procedure i am creating. But the problem here is, ENTRYDATE which is going in new table should have just one digit for month and one digit for year as stated below, day is not required

Jan to Sep is represented by 1 to 9

Oct with '0'

Nov with '-'

Dec with '&'

Year should be one digit for example if it is 2007 than it should be represented as 7 the values are from 2000 to 2007

p.entrydate is in datetime format and entrydate in herm_inmar_pre is VARCHAR(2)

|||

A bit of CASE should do the trick here:

declare @.entrydate datetime
set @.entrydate = '22 nov 2003'

SELECT (CASE DATEPART(M, @.entrydate)
WHEN 10 THEN '0'
WHEN 11 THEN '-'
WHEN 12 THEN '￡'
ELSE CAST(DATEPART(M, @.entrydate) AS CHAR(1))
END + LEFT(REVERSE(DATENAME(YY, @.entrydate)),1)) AS DateAbbrev

HTH!