conctenation error in sqlserver 2008

Posted by Klbaiju under Sql Server on 9/24/2013 | Points: 10 | Views : 1220 | Status : [Member] | Replies : 5
Hi,

I want to concatenate a date like this

select datename(YEAR,GETDATE())+'-'+datename(MONTH,GETDATE())

the result of this query is

2013-september

I want the output should be like this

2013-09-01

I tried this

select datename(YEAR,GETDATE())+'-'+datename(M,GETDATE())+'-01

but showing error

i need the output like this

2013-09-01

how to solve this

Regards

Baiju




Responses

Posted by: Bandi on: 9/24/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
USE DATEPART()....
select datename(YEAR,GETDATE())+'-'+CAST(datePART(MM,GETDATE()) AS VARCHAR(3))+ '-01'

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/24/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
If you use DATENAME() it returns September instead of 9

select datename(YEAR,GETDATE())+'-'+datename(M,GETDATE())+ '-01'

OUTPUT:
(No column name)
2013-September-01


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/24/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
You doesn't need complex ways to get YYYY-MM-DD format for DATETIME Variable

Simply


DECLARE @date DATETIME = GETDATE() -- here @date is variable of type DATETIME
SELECT CONVERT(VARCHAR(26), @date, 23) [YYYY-MM-DD]

--OUTPUT:
YYYY-MM-DD
2013-09-24


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/24/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
In your code you missed out closed quotation....
select datename(YEAR,GETDATE())+'-'+datename(M,GETDATE())+'-01 


The above should be
select datename(YEAR,GETDATE())+'-'+datename(M,GETDATE())+'-01'  


The above will give you "2013-September-01 " format

For you requirement

select datename(YEAR,GETDATE())+'-'+cast(datepart(MM,GETDATE()) as varchaR(3))+'-01' -- return "2013-9-01 "
SELECT CONVERT(VARCHAR(26), GETDATE(), 23) -- 2013-09-24


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 9/24/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Mark As Answer if you sorted out the issue; Otherwise post us back with clear explanation

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response