conctenation error in sqlserver 2008

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

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

0	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

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

0	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

Latest Posts