February 8, 2010 at 4:31 pm
I am trying to get a YYWW format from getdate(). I was able to do it like this
SELECT YYWW = CASE WHEN
LEN(CAST(DATEPART(ww,GETDATE())as VARCHAR(2))) = 2
THEN
CAST(RIGHT(CAST(DATEPART(yy,GETDATE())as VARCHAR(4)),2) AS CHAR(2))+CAST(DATEPART(ww,GETDATE())as VARCHAR(2))
ELSE
CAST(RIGHT(CAST(DATEPART(yy,GETDATE())as VARCHAR(4)),2) AS CHAR(2))+'0'+CAST(DATEPART(ww,GETDATE())as VARCHAR(2))
END
but I'm sure someone knows a much better way.
Thanks in advance!!
February 8, 2010 at 5:01 pm
That is pretty efficient as it is. There are other methods - however, what you are using is quite adequate.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 8, 2010 at 5:07 pm
This version dispenses with CASE:
SELECT YYWW = CAST( RIGHT( CAST( DATEPART(yy,GETDATE() ) as VARCHAR(4) ), 2 ) AS CHAR(2) ) +
RIGHT( '0' + CAST( DATEPART(ww,GETDATE() ) as VARCHAR(2) ), 2 )
February 8, 2010 at 5:11 pm
Nice. More efficient. I hadn't considered this method. Makes sense though.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 9, 2010 at 2:02 am
CirquedeSQLeil (2/8/2010)
I hadn't considered this method. Makes sense though.
Hi Janson,
Is there any direct convert method to get the year and week from date time?
I thought this code having some sense
select convert(char(2),getdate(),12)+
right( '0' + cast( datepart(ww,getdate() ) as varchar(2) ), 2 )
/*or using this to get week*/
replace(str(datepart(ww,getdate()),2,0),' ','0')
February 9, 2010 at 8:32 am
Excellent! Thanks so much for the help.
February 9, 2010 at 10:32 am
arun.sas (2/9/2010)
CirquedeSQLeil (2/8/2010)
I hadn't considered this method. Makes sense though.Hi Janson,
Is there any direct convert method to get the year and week from date time?
I thought this code having some sense
select convert(char(2),getdate(),12)+right( '0' + cast( datepart(ww,getdate() ) as varchar(2) ), 2 )
/*or using this to get week*/
replace(str(datepart(ww,getdate()),2,0),' ','0')
No direct way - you need to concatenate as you have done.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply