May 17, 2005 at 1:55 pm
Hi, I need to format a datetime field in a query to 'yyyy-mm'. When I run:
SELECT ((DatePart(year, datefield)) + '-' + (DatePart(month, datefield))) AS PaidMonth
I only get the year as a result. Can someone point me in the right direction? Thanks.
May 17, 2005 at 2:02 pm
DatePart returns a number, not a string. Use convert to change the datepart to a string.
SELECT convert(varchar(4), DatePart(year, datefield))
+ '-'
+ convert(varchar(2), DatePart(month, datefield))
Let me know if it helped.
May 17, 2005 at 2:12 pm
Thanks ! What would be a good way to ensure the month field has a zero in front of it if it is a singled digit month, so I get '08' instead of 8? Thanks for your help !
May 17, 2005 at 2:39 pm
SELECT convert(varchar, DatePart(year, getdate()))
+ '-'
+ RIGHT('0' + convert(varchar(2), DatePart(month, getdate())), 2)
May 17, 2005 at 2:45 pm
In addition to Remi, IF you know that your field will always be 4 I would change the CONVERT(VARCHAR, to be CONVERT(VARCHAR(4), or CONVERT(CHAR(4),
I have had instances in the past where a conversion without length has returned only the 1st character .....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
May 17, 2005 at 6:21 pm
This seems to do the trick.
select convert(char(07),getdate(),120)
---------------------------------------
2005-05
May 18, 2005 at 2:33 pm
Thanks so much for your help guys !
One more question. I'm converting an access database to SQL. I have the following query in Access:
UPDATE XDummyPaidMonth " & _
" SET PaidMonth = iif([MonthCnt]=49,' PRIOR',Format(DateAdd('m',-[MonthCnt],Date$()),'yyyy-mm'));"
Would this be its SQL equivalent:
UPDATE XDummyPaidMonth
SET PaidMonth = CASE WHEN MonthCnt = 49 THEN 'PRIOR'
ELSE convert(char(07),(DateAdd(month, -MonthCnt, getdate())),120)
END
Thanks again.
May 18, 2005 at 2:39 pm
That looks ok to me... did you test it?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply