How to get the date part from a date with timestamp column in SQL Server?
I want to get the date in DDMMYYY format from a field that stores date in YYYY-MM-DD Timestamp format. (For example, '2018-11-09' should display '09Nov2018').
I know that one way is to use the datepart function (https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017) which can extract the year, month and day individually and then I can concat them.
Edit: I don't want to use getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using:
concat
(
CONVERT(varchar, DATEPART(dd,columndate)),
CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) ,
CONVERT(varchar, DATEPART(year, columndate))
) AS trial
This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to `datetype' again as that is how I want the result.
Is there any other way to achieve this?
Any ideas/suggestions much appreciated.
sql-server sql-server-2008
edited Nov 12 at 14:37
Aaron Bertrand♦
149k18282480
asked Nov 12 at 13:26
T.H.
925
add a comment |
I want to get the date in DDMMYYY format from a field that stores date in YYYY-MM-DD Timestamp format. (For example, '2018-11-09' should display '09Nov2018').
I know that one way is to use the datepart function (https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017) which can extract the year, month and day individually and then I can concat them.
Edit: I don't want to use getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using:
concat
(
CONVERT(varchar, DATEPART(dd,columndate)),
CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) ,
CONVERT(varchar, DATEPART(year, columndate))
) AS trial
This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to `datetype' again as that is how I want the result.
Is there any other way to achieve this?
Any ideas/suggestions much appreciated.
sql-server sql-server-2008
edited Nov 12 at 14:37
Aaron Bertrand♦
149k18282480
asked Nov 12 at 13:26
T.H.
925
1
If the data type isdate, then it doesn't store it in any format, that is just how SSMS chooses to show it to you.
– Aaron Bertrand♦
Nov 12 at 13:37
In general, you can use either the CONVERT() function with a suitable formatting code, and massage the result to your linking (chop off the beginning, end, replace unwanted characters with nothing etc). Or use FORMAT() with which you can specify a "pattern" for the returned string. Format uses more CPU, though.
– Tibor Karaszi
Nov 12 at 13:41
How are you usingCONCAT()in SQL Server 2008? Also, please read this about yourconvert()calls.
– Aaron Bertrand♦
Nov 12 at 14:37
I don't know how it is working but I tried and it worked.
– T.H.
Nov 12 at 15:57
Then you're not using SQL Server 2008 (which is how you tagged your question), sinceCONCAT()was introduced in SQL Server 2012.
– Aaron Bertrand♦
Nov 12 at 17:10
add a comment |
I want to get the date in DDMMYYY format from a field that stores date in YYYY-MM-DD Timestamp format. (For example, '2018-11-09' should display '09Nov2018').
I know that one way is to use the datepart function (https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017) which can extract the year, month and day individually and then I can concat them.
Edit: I don't want to use getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using:
concat
(
CONVERT(varchar, DATEPART(dd,columndate)),
CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) ,
CONVERT(varchar, DATEPART(year, columndate))
) AS trial
This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to `datetype' again as that is how I want the result.
Is there any other way to achieve this?
Any ideas/suggestions much appreciated.
sql-server sql-server-2008
edited Nov 12 at 14:37
Aaron Bertrand♦
149k18282480
asked Nov 12 at 13:26
T.H.
925
I want to get the date in DDMMYYY format from a field that stores date in YYYY-MM-DD Timestamp format. (For example, '2018-11-09' should display '09Nov2018').
I know that one way is to use the datepart function (https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017) which can extract the year, month and day individually and then I can concat them.
Edit: I don't want to use getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using:
concat
(
CONVERT(varchar, DATEPART(dd,columndate)),
CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) ,
CONVERT(varchar, DATEPART(year, columndate))
) AS trial
This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to `datetype' again as that is how I want the result.
Is there any other way to achieve this?
Any ideas/suggestions much appreciated.
sql-server sql-server-2008
sql-server sql-server-2008
edited Nov 12 at 14:37
Aaron Bertrand♦
149k18282480
asked Nov 12 at 13:26
T.H.
925
edited Nov 12 at 14:37
Aaron Bertrand♦
149k18282480
asked Nov 12 at 13:26
T.H.
925
edited Nov 12 at 14:37
Aaron Bertrand♦
149k18282480
edited Nov 12 at 14:37
Aaron Bertrand♦
149k18282480
edited Nov 12 at 14:37
Aaron Bertrand♦
149k18282480
149k18282480
asked Nov 12 at 13:26
T.H.
925
asked Nov 12 at 13:26
T.H.
925
asked Nov 12 at 13:26
T.H.
925
925
1
If the data type isdate, then it doesn't store it in any format, that is just how SSMS chooses to show it to you.
– Aaron Bertrand♦
Nov 12 at 13:37
In general, you can use either the CONVERT() function with a suitable formatting code, and massage the result to your linking (chop off the beginning, end, replace unwanted characters with nothing etc). Or use FORMAT() with which you can specify a "pattern" for the returned string. Format uses more CPU, though.
– Tibor Karaszi
Nov 12 at 13:41
How are you usingCONCAT()in SQL Server 2008? Also, please read this about yourconvert()calls.
– Aaron Bertrand♦
Nov 12 at 14:37
I don't know how it is working but I tried and it worked.
– T.H.
Nov 12 at 15:57
Then you're not using SQL Server 2008 (which is how you tagged your question), sinceCONCAT()was introduced in SQL Server 2012.
– Aaron Bertrand♦
Nov 12 at 17:10
add a comment |
1
If the data type isdate, then it doesn't store it in any format, that is just how SSMS chooses to show it to you.
– Aaron Bertrand♦
Nov 12 at 13:37
In general, you can use either the CONVERT() function with a suitable formatting code, and massage the result to your linking (chop off the beginning, end, replace unwanted characters with nothing etc). Or use FORMAT() with which you can specify a "pattern" for the returned string. Format uses more CPU, though.
– Tibor Karaszi
Nov 12 at 13:41
How are you usingCONCAT()in SQL Server 2008? Also, please read this about yourconvert()calls.
– Aaron Bertrand♦
Nov 12 at 14:37
I don't know how it is working but I tried and it worked.
– T.H.
Nov 12 at 15:57
Then you're not using SQL Server 2008 (which is how you tagged your question), sinceCONCAT()was introduced in SQL Server 2012.
– Aaron Bertrand♦
Nov 12 at 17:10
1
1
If the data type is
date, then it doesn't store it in any format, that is just how SSMS chooses to show it to you.– Aaron Bertrand♦
Nov 12 at 13:37
If the data type is
date, then it doesn't store it in any format, that is just how SSMS chooses to show it to you.– Aaron Bertrand♦
Nov 12 at 13:37
In general, you can use either the CONVERT() function with a suitable formatting code, and massage the result to your linking (chop off the beginning, end, replace unwanted characters with nothing etc). Or use FORMAT() with which you can specify a "pattern" for the returned string. Format uses more CPU, though.
– Tibor Karaszi
Nov 12 at 13:41
In general, you can use either the CONVERT() function with a suitable formatting code, and massage the result to your linking (chop off the beginning, end, replace unwanted characters with nothing etc). Or use FORMAT() with which you can specify a "pattern" for the returned string. Format uses more CPU, though.
– Tibor Karaszi
Nov 12 at 13:41
How are you using
CONCAT() in SQL Server 2008? Also, please read this about your convert() calls.– Aaron Bertrand♦
Nov 12 at 14:37
How are you using
CONCAT() in SQL Server 2008? Also, please read this about your convert() calls.– Aaron Bertrand♦
Nov 12 at 14:37
I don't know how it is working but I tried and it worked.
– T.H.
Nov 12 at 15:57
I don't know how it is working but I tried and it worked.
– T.H.
Nov 12 at 15:57
Then you're not using SQL Server 2008 (which is how you tagged your question), since
CONCAT() was introduced in SQL Server 2012.– Aaron Bertrand♦
Nov 12 at 17:10
Then you're not using SQL Server 2008 (which is how you tagged your question), since
CONCAT() was introduced in SQL Server 2012.– Aaron Bertrand♦
Nov 12 at 17:10
add a comment |
2 Answers
2
active
oldest
votes
Here is an example using GETDATE():
SELECT REPLACE(CONVERT(char(11), GETDATE(), 13),' ','');
Yields 12Nov2018. Three days ago, it would yield 09Nov2018.
To run this against your table:
SELECT Trial = REPLACE(CONVERT(char(11), YOUR_COLUMN_NAME_HERE, 13),' ','')
FROM dbo.YOUR_TABLE_NAME_HERE;
In SQL Server 2012 and above, you can use FORMAT() but, like Tibor suggested, this uses more CPU (roughly doubling runtime in my tests).
SELECT FORMAT(GETDATE(), 'ddMMMyyyy');
answered Nov 12 at 13:40
Aaron Bertrand♦
149k18282480
Thank you for the answer but I can't use this as my reference date is not getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using concat(CONVERT(varchar, DATEPART(dd,columndate)), CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , CONVERT(varchar, DATEPART(year, columndate))) AS trial. This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to datetype again as that is how I want the result.
– T.H.
Nov 12 at 13:50
1
@T.H. you replaceGETDATE()in my statement with the column name in your query. Your question didn't tell us either the name of the column or the name of the table, so I showed a sample that worked against a function instead.
– Aaron Bertrand♦
Nov 12 at 13:52
Apologies about that, I will update the question. Thanks a lot.
– T.H.
Nov 12 at 14:01
add a comment |
SELECT Replace(Convert(nvarchar(100),YourDate,106),' ','') gives the desired result
answered Nov 12 at 16:20
Momo
1
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222342%2fhow-to-get-the-date-part-from-a-date-with-timestamp-column-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Here is an example using GETDATE():
SELECT REPLACE(CONVERT(char(11), GETDATE(), 13),' ','');
Yields 12Nov2018. Three days ago, it would yield 09Nov2018.
To run this against your table:
SELECT Trial = REPLACE(CONVERT(char(11), YOUR_COLUMN_NAME_HERE, 13),' ','')
FROM dbo.YOUR_TABLE_NAME_HERE;
In SQL Server 2012 and above, you can use FORMAT() but, like Tibor suggested, this uses more CPU (roughly doubling runtime in my tests).
SELECT FORMAT(GETDATE(), 'ddMMMyyyy');
answered Nov 12 at 13:40
Aaron Bertrand♦
149k18282480
Thank you for the answer but I can't use this as my reference date is not getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using concat(CONVERT(varchar, DATEPART(dd,columndate)), CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , CONVERT(varchar, DATEPART(year, columndate))) AS trial. This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to datetype again as that is how I want the result.
– T.H.
Nov 12 at 13:50
1
@T.H. you replaceGETDATE()in my statement with the column name in your query. Your question didn't tell us either the name of the column or the name of the table, so I showed a sample that worked against a function instead.
– Aaron Bertrand♦
Nov 12 at 13:52
Apologies about that, I will update the question. Thanks a lot.
– T.H.
Nov 12 at 14:01
add a comment |
Here is an example using GETDATE():
SELECT REPLACE(CONVERT(char(11), GETDATE(), 13),' ','');
Yields 12Nov2018. Three days ago, it would yield 09Nov2018.
To run this against your table:
SELECT Trial = REPLACE(CONVERT(char(11), YOUR_COLUMN_NAME_HERE, 13),' ','')
FROM dbo.YOUR_TABLE_NAME_HERE;
In SQL Server 2012 and above, you can use FORMAT() but, like Tibor suggested, this uses more CPU (roughly doubling runtime in my tests).
SELECT FORMAT(GETDATE(), 'ddMMMyyyy');
answered Nov 12 at 13:40
Aaron Bertrand♦
149k18282480
Thank you for the answer but I can't use this as my reference date is not getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using concat(CONVERT(varchar, DATEPART(dd,columndate)), CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , CONVERT(varchar, DATEPART(year, columndate))) AS trial. This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to datetype again as that is how I want the result.
– T.H.
Nov 12 at 13:50
1
@T.H. you replaceGETDATE()in my statement with the column name in your query. Your question didn't tell us either the name of the column or the name of the table, so I showed a sample that worked against a function instead.
– Aaron Bertrand♦
Nov 12 at 13:52
Apologies about that, I will update the question. Thanks a lot.
– T.H.
Nov 12 at 14:01
add a comment |
Here is an example using GETDATE():
SELECT REPLACE(CONVERT(char(11), GETDATE(), 13),' ','');
Yields 12Nov2018. Three days ago, it would yield 09Nov2018.
To run this against your table:
SELECT Trial = REPLACE(CONVERT(char(11), YOUR_COLUMN_NAME_HERE, 13),' ','')
FROM dbo.YOUR_TABLE_NAME_HERE;
In SQL Server 2012 and above, you can use FORMAT() but, like Tibor suggested, this uses more CPU (roughly doubling runtime in my tests).
SELECT FORMAT(GETDATE(), 'ddMMMyyyy');
answered Nov 12 at 13:40
Aaron Bertrand♦
149k18282480
Here is an example using GETDATE():
SELECT REPLACE(CONVERT(char(11), GETDATE(), 13),' ','');
Yields 12Nov2018. Three days ago, it would yield 09Nov2018.
To run this against your table:
SELECT Trial = REPLACE(CONVERT(char(11), YOUR_COLUMN_NAME_HERE, 13),' ','')
FROM dbo.YOUR_TABLE_NAME_HERE;
In SQL Server 2012 and above, you can use FORMAT() but, like Tibor suggested, this uses more CPU (roughly doubling runtime in my tests).
SELECT FORMAT(GETDATE(), 'ddMMMyyyy');
answered Nov 12 at 13:40
Aaron Bertrand♦
149k18282480
edited Nov 12 at 13:53
answered Nov 12 at 13:40
Aaron Bertrand♦
149k18282480
answered Nov 12 at 13:40
Aaron Bertrand♦
149k18282480
answered Nov 12 at 13:40
Aaron Bertrand♦
149k18282480
149k18282480
Thank you for the answer but I can't use this as my reference date is not getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using concat(CONVERT(varchar, DATEPART(dd,columndate)), CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , CONVERT(varchar, DATEPART(year, columndate))) AS trial. This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to datetype again as that is how I want the result.
– T.H.
Nov 12 at 13:50
1
@T.H. you replaceGETDATE()in my statement with the column name in your query. Your question didn't tell us either the name of the column or the name of the table, so I showed a sample that worked against a function instead.
– Aaron Bertrand♦
Nov 12 at 13:52
Apologies about that, I will update the question. Thanks a lot.
– T.H.
Nov 12 at 14:01
add a comment |
Thank you for the answer but I can't use this as my reference date is not getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using concat(CONVERT(varchar, DATEPART(dd,columndate)), CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , CONVERT(varchar, DATEPART(year, columndate))) AS trial. This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to datetype again as that is how I want the result.
– T.H.
Nov 12 at 13:50
1
@T.H. you replaceGETDATE()in my statement with the column name in your query. Your question didn't tell us either the name of the column or the name of the table, so I showed a sample that worked against a function instead.
– Aaron Bertrand♦
Nov 12 at 13:52
Apologies about that, I will update the question. Thanks a lot.
– T.H.
Nov 12 at 14:01
Thank you for the answer but I can't use this as my reference date is not getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using concat(CONVERT(varchar, DATEPART(dd,columndate)), CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , CONVERT(varchar, DATEPART(year, columndate))) AS trial. This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to datetype again as that is how I want the result.
– T.H.
Nov 12 at 13:50
Thank you for the answer but I can't use this as my reference date is not getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using concat(CONVERT(varchar, DATEPART(dd,columndate)), CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , CONVERT(varchar, DATEPART(year, columndate))) AS trial. This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to datetype again as that is how I want the result.
– T.H.
Nov 12 at 13:50
1
1
@T.H. you replace
GETDATE() in my statement with the column name in your query. Your question didn't tell us either the name of the column or the name of the table, so I showed a sample that worked against a function instead.– Aaron Bertrand♦
Nov 12 at 13:52
@T.H. you replace
GETDATE() in my statement with the column name in your query. Your question didn't tell us either the name of the column or the name of the table, so I showed a sample that worked against a function instead.– Aaron Bertrand♦
Nov 12 at 13:52
Apologies about that, I will update the question. Thanks a lot.
– T.H.
Nov 12 at 14:01
Apologies about that, I will update the question. Thanks a lot.
– T.H.
Nov 12 at 14:01
add a comment |
SELECT Replace(Convert(nvarchar(100),YourDate,106),' ','') gives the desired result
answered Nov 12 at 16:20
Momo
1
add a comment |
SELECT Replace(Convert(nvarchar(100),YourDate,106),' ','') gives the desired result
answered Nov 12 at 16:20
Momo
1
add a comment |
SELECT Replace(Convert(nvarchar(100),YourDate,106),' ','') gives the desired result
answered Nov 12 at 16:20
Momo
1
SELECT Replace(Convert(nvarchar(100),YourDate,106),' ','') gives the desired result
answered Nov 12 at 16:20
Momo
1
answered Nov 12 at 16:20
Momo
1
answered Nov 12 at 16:20
Momo
1
answered Nov 12 at 16:20
Momo
1
1
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222342%2fhow-to-get-the-date-part-from-a-date-with-timestamp-column-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
If the data type is
date, then it doesn't store it in any format, that is just how SSMS chooses to show it to you.– Aaron Bertrand♦
Nov 12 at 13:37
In general, you can use either the CONVERT() function with a suitable formatting code, and massage the result to your linking (chop off the beginning, end, replace unwanted characters with nothing etc). Or use FORMAT() with which you can specify a "pattern" for the returned string. Format uses more CPU, though.
– Tibor Karaszi
Nov 12 at 13:41
How are you using
CONCAT()in SQL Server 2008? Also, please read this about yourconvert()calls.– Aaron Bertrand♦
Nov 12 at 14:37
I don't know how it is working but I tried and it worked.
– T.H.
Nov 12 at 15:57
Then you're not using SQL Server 2008 (which is how you tagged your question), since
CONCAT()was introduced in SQL Server 2012.– Aaron Bertrand♦
Nov 12 at 17:10