How to convert DateTime to VarChar
266
I am working on a query in Sql Server 2005 where I need to convert a value in DateTime variable into a varchar variable in yyyy-mm-dd format (without time part). How do I do that?
sql
sql-server datetime sql-convert edited Sep 21 '15 at 8:05
Dyrandz Famador
3,78251534
asked Sep 16 '08 at 16:44
AliAli
2,29962028
add a comment |
266
I am working on a query in Sql Server 2005 where I need to convert a value in DateTime variable into a varchar variable in yyyy-mm-dd format (without time part). How do I do that?
sql
sql-server datetime sql-convert edited Sep 21 '15 at 8:05
Dyrandz Famador
3,78251534
asked Sep 16 '08 at 16:44
AliAli
2,29962028
CONVERT, see MSDN documentation.
– Jason Cohen
Sep 16 '08 at 16:46
be aware that YYYY-MM-DD is ambiguous, depending on your language settings. best to use ISO standard YYYYMMDD, see this blog post
– Andy Irving
Sep 16 '08 at 17:49
add a comment |
266
266
266
75
I am working on a query in Sql Server 2005 where I need to convert a value in DateTime variable into a varchar variable in yyyy-mm-dd format (without time part). How do I do that?
sql
sql-server datetime sql-convert edited Sep 21 '15 at 8:05
Dyrandz Famador
3,78251534
asked Sep 16 '08 at 16:44
AliAli
2,29962028
I am working on a query in Sql Server 2005 where I need to convert a value in DateTime variable into a varchar variable in yyyy-mm-dd format (without time part). How do I do that?
sql
sql-server datetime sql-convert sql
sql-server datetime sql-convert edited Sep 21 '15 at 8:05
Dyrandz Famador
3,78251534
asked Sep 16 '08 at 16:44
AliAli
2,29962028
edited Sep 21 '15 at 8:05
Dyrandz Famador
3,78251534
asked Sep 16 '08 at 16:44
AliAli
2,29962028
edited Sep 21 '15 at 8:05
Dyrandz Famador
3,78251534
edited Sep 21 '15 at 8:05
Dyrandz Famador
3,78251534
edited Sep 21 '15 at 8:05
Dyrandz Famador
3,78251534
3,78251534
asked Sep 16 '08 at 16:44
AliAli
2,29962028
asked Sep 16 '08 at 16:44
AliAli
2,29962028
asked Sep 16 '08 at 16:44
AliAli
2,29962028
2,29962028
CONVERT, see MSDN documentation.
– Jason Cohen
Sep 16 '08 at 16:46
be aware that YYYY-MM-DD is ambiguous, depending on your language settings. best to use ISO standard YYYYMMDD, see this blog post
– Andy Irving
Sep 16 '08 at 17:49
add a comment |
CONVERT, see MSDN documentation.
– Jason Cohen
Sep 16 '08 at 16:46
be aware that YYYY-MM-DD is ambiguous, depending on your language settings. best to use ISO standard YYYYMMDD, see this blog post
– Andy Irving
Sep 16 '08 at 17:49
CONVERT, see MSDN documentation.– Jason Cohen
Sep 16 '08 at 16:46
CONVERT, see MSDN documentation.– Jason Cohen
Sep 16 '08 at 16:46
be aware that YYYY-MM-DD is ambiguous, depending on your language settings. best to use ISO standard YYYYMMDD, see this blog post
– Andy Irving
Sep 16 '08 at 17:49
be aware that YYYY-MM-DD is ambiguous, depending on your language settings. best to use ISO standard YYYYMMDD, see this blog post
– Andy Irving
Sep 16 '08 at 17:49
add a comment |
21 Answers
21
active
oldest
votes
238
With Microsoft Sql Server:
--
-- Create test case
--
DECLARE @myDateTime DATETIME
SET @myDateTime = '2008-05-03'
--
-- Convert string
--
SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)
answered Sep 16 '08 at 16:52
TonyOssaTonyOssa
2,93711314
9
For those of you who may be interested, this would perform better if you change VARCHAR to CHAR. See this post (stackoverflow.com/questions/59667/…) for more details. Essentially, there are 2 bytes of overhead involved in VARCHAR vs CHAR. In this scenario, we know that your string will always be 10 characters, so CHAR is appropriate.
– Will Ediger
Dec 30 '14 at 23:04
Side note, from what I've seen, too bad none of the formats honors whatever the DATEFORMAT is set to, maybe there is another way.
– Alex Nolasco
May 15 '15 at 16:07
1
Is there any reason to useLEFT(.., 10)instead ofCONVERT(CHAR(10), ...)? Also those working with the newer versions of SQL Server than 2005(!) should check out the answer by Zar Shardan suggesting a solution based onFORMAT(date_value, format_string)function.
– Nickolay
Jan 25 '17 at 15:28
@Nickolay besidesFORMAT()is too slow relative toconvert(char(10),...)
– abdul qayyum
Nov 2 '17 at 10:08
add a comment |
329
Here's some test sql for all the styles.
DECLARE @now datetime
SET @now = GETDATE()
select convert(nvarchar(MAX), @now, 0) as output, 0 as style
union select convert(nvarchar(MAX), @now, 1), 1
union select convert(nvarchar(MAX), @now, 2), 2
union select convert(nvarchar(MAX), @now, 3), 3
union select convert(nvarchar(MAX), @now, 4), 4
union select convert(nvarchar(MAX), @now, 5), 5
union select convert(nvarchar(MAX), @now, 6), 6
union select convert(nvarchar(MAX), @now, 7), 7
union select convert(nvarchar(MAX), @now, 8), 8
union select convert(nvarchar(MAX), @now, 9), 9
union select convert(nvarchar(MAX), @now, 10), 10
union select convert(nvarchar(MAX), @now, 11), 11
union select convert(nvarchar(MAX), @now, 12), 12
union select convert(nvarchar(MAX), @now, 13), 13
union select convert(nvarchar(MAX), @now, 14), 14
--15 to 19 not valid
union select convert(nvarchar(MAX), @now, 20), 20
union select convert(nvarchar(MAX), @now, 21), 21
union select convert(nvarchar(MAX), @now, 22), 22
union select convert(nvarchar(MAX), @now, 23), 23
union select convert(nvarchar(MAX), @now, 24), 24
union select convert(nvarchar(MAX), @now, 25), 25
--26 to 99 not valid
union select convert(nvarchar(MAX), @now, 100), 100
union select convert(nvarchar(MAX), @now, 101), 101
union select convert(nvarchar(MAX), @now, 102), 102
union select convert(nvarchar(MAX), @now, 103), 103
union select convert(nvarchar(MAX), @now, 104), 104
union select convert(nvarchar(MAX), @now, 105), 105
union select convert(nvarchar(MAX), @now, 106), 106
union select convert(nvarchar(MAX), @now, 107), 107
union select convert(nvarchar(MAX), @now, 108), 108
union select convert(nvarchar(MAX), @now, 109), 109
union select convert(nvarchar(MAX), @now, 110), 110
union select convert(nvarchar(MAX), @now, 111), 111
union select convert(nvarchar(MAX), @now, 112), 112
union select convert(nvarchar(MAX), @now, 113), 113
union select convert(nvarchar(MAX), @now, 114), 114
union select convert(nvarchar(MAX), @now, 120), 120
union select convert(nvarchar(MAX), @now, 121), 121
--122 to 125 not valid
union select convert(nvarchar(MAX), @now, 126), 126
union select convert(nvarchar(MAX), @now, 127), 127
--128, 129 not valid
union select convert(nvarchar(MAX), @now, 130), 130
union select convert(nvarchar(MAX), @now, 131), 131
--132 not valid
order BY style
Here's the result
output style
Apr 28 2014 9:31AM 0
04/28/14 1
14.04.28 2
28/04/14 3
28.04.14 4
28-04-14 5
28 Apr 14 6
Apr 28, 14 7
09:31:28 8
Apr 28 2014 9:31:28:580AM 9
04-28-14 10
14/04/28 11
140428 12
28 Apr 2014 09:31:28:580 13
09:31:28:580 14
2014-04-28 09:31:28 20
2014-04-28 09:31:28.580 21
04/28/14 9:31:28 AM 22
2014-04-28 23
09:31:28 24
2014-04-28 09:31:28.580 25
Apr 28 2014 9:31AM 100
04/28/2014 101
2014.04.28 102
28/04/2014 103
28.04.2014 104
28-04-2014 105
28 Apr 2014 106
Apr 28, 2014 107
09:31:28 108
Apr 28 2014 9:31:28:580AM 109
04-28-2014 110
2014/04/28 111
20140428 112
28 Apr 2014 09:31:28:580 113
09:31:28:580 114
2014-04-28 09:31:28 120
2014-04-28 09:31:28.580 121
2014-04-28T09:31:28.580 126
2014-04-28T09:31:28.580 127
28 جمادى الثانية 1435 9:31:28:580AM 130
28/06/1435 9:31:28:580AM 131
Make nvarchar(max) shorter to trim the time. For example:
select convert(nvarchar(11), GETDATE(), 0)
union select convert(nvarchar(max), GETDATE(), 0)
outputs:
May 18 2018
May 18 2018 9:57AM
answered Oct 23 '13 at 9:16
ColinColin
16.8k1583153
Here Sometimes we want likedd-mmorjun - 28. There is any option??
– Rock
May 24 '16 at 14:32
Works for SQL Server 2005 so this is great for those of us that are still unfortunately meddling with older systems. Upgrading to 2014 soon, excited!
– dyslexicanaboko
Aug 26 '16 at 17:38
I wanna format close to 101 -- 101 = 04/28/2014 , I wanna without zero in the month , output 4/28/2014, is it applicable ?
– ahmed abdelqader
Nov 10 '16 at 7:35
1
Isn't the timezone identifier Z missing in style 127.
– Atishay
Sep 1 '17 at 7:48
@Atishay "only supported when casting from character data to datetime or smalldatetime". See the footnotes 6 and 7 at docs.microsoft.com/de-de/sql/t-sql/functions/…
– Colin
Sep 4 '17 at 11:15
|
show 1 more comment
179
Try the following:
CONVERT(varchar(10), [MyDateTimecolumn], 20)
For a full date time and not just date do:
CONVERT(varchar(23), [MyDateTimecolumn], 121)
See this page for convert styles:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
OR
SQL Server CONVERT() Function
edited May 31 '15 at 13:09
Yakir Manor
3,77312123
answered Sep 16 '08 at 16:45
Joel CoehoornJoel Coehoorn
308k95490721
add a comment |
27
SQL Server 2012 has a new function , FORMAT:
http://msdn.microsoft.com/en-us/library/ee634924.aspx
and you can use custom date time format strings: http://msdn.microsoft.com/en-us/library/ee634398.aspx
These pages imply it is also available on SQL2008R2, but I don't have one handy to test if that's the case.
Example usage (Australian datetime):
FORMAT(VALUE,'dd/MM/yyyy h:mm:ss tt')
answered Jul 18 '13 at 2:58
Zar ShardanZar Shardan
4,10112731
add a comment |
9
Either Cast or Convert:
Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Actually since you asked for a specific format:
REPLACE(CONVERT(varchar(10), Date, 102), '.', '-')
edited May 11 '11 at 22:34
BlueRaja - Danny Pflughoeft
58k21152241
answered Sep 16 '08 at 16:46
Per Hornshøj-SchierbeckPer Hornshøj-Schierbeck
8,668177094
add a comment |
8
You can use DATEPART(DATEPART, VARIABLE). For example:
DECLARE @DAY INT
DECLARE @MONTH INT
DECLARE @YEAR INT
DECLARE @DATE DATETIME
@DATE = GETDATE()
SELECT @DAY = DATEPART(DAY,@DATE)
SELECT @MONTH = DATEPART(MONTH,@DATE)
SELECT @YEAR = DATEPART(YEAR,@DATE)
edited Jul 20 '12 at 21:47
kay
18.3k968115
answered Jul 20 '12 at 21:42
FCKOEFCKOE
8111
add a comment |
5
-- This gives you the time as 0 in format 'yyyy-mm-dd 00:00:00.000'
SELECT CAST( CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME) ;
answered Jun 21 '11 at 18:45
P's-SQLP's-SQL
5112
add a comment |
4
With Microsoft SQL Server:
Use Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Example:
SELECT CONVERT(varchar,d.dateValue,1-9)
For the style you can find more info here: MSDN - Cast and Convert (Transact-SQL).
edited Oct 20 '12 at 8:51
Pete Carter
2,44721833
answered May 30 '12 at 15:44
dmunozpadmunozpa
967
add a comment |
2
Try:
select replace(convert(varchar, getdate(), 111),'/','-');
More on ms sql tips
answered Jun 6 '11 at 15:32
Arek BeeArek Bee
15627
add a comment |
2
Try the following:
CONVERT(VARCHAR(10),GetDate(),102)
Then you would need to replace the "." with "-".
Here is a site that helps
http://www.mssqltips.com/tip.asp?tip=1145
edited Apr 14 '14 at 10:54
DMK
2,03111733
answered Sep 16 '08 at 16:50
Amy PattersonAmy Patterson
3241624
add a comment |
2
declare @dt datetime
set @dt = getdate()
select convert(char(10),@dt,120)
I have fixed data length of char(10) as you want a specific string format.
edited Apr 22 '14 at 7:22
Dawson Loudon
5,85122229
answered Sep 26 '08 at 9:33
Andy JonesAndy Jones
1,255813
add a comment |
1
The OP mentioned datetime format. For me, the time part gets in the way.
I think it's a bit cleaner to remove the time portion (by casting datetime to date) before formatting.
convert( varchar(10), convert( date, @yourDate ) , 111 )
answered Aug 12 '11 at 13:38
m42m42
2,07242231
add a comment |
1
This is how I do it: CONVERT(NVARCHAR(10), DATE1, 103) )
edited Aug 11 '13 at 20:50
FabianCook
11.9k144996
answered Mar 25 '13 at 17:31
IvanSnekIvanSnek
8614
add a comment |
1
You can convert your date in many formats, the syntaxe is simple to use :
CONVERT('TheTypeYouWant', 'TheDateToConvert', 'TheCodeForFormating' * )
CONVERT(NVARCHAR(10), DATE_OF_DAY, 103) => 15/09/2016
- The code is an integer, here 3 is the third formating without century, if you want the century just change the code to 103.
In your case, i've just converted and restrict size by nvarchar(10) like this :
CONVERT(NVARCHAR(10), MY_DATE_TIME, 120) => 2016-09-15
See more at : http://www.w3schools.com/sql/func_convert.asp
Another solution (if your date is a Datetime) is a simple CAST :
CAST(MY_DATE_TIME as DATE) => 2016-09-15
answered Jan 11 '17 at 15:37
Ema.HEma.H
1,81732032
add a comment |
1
Try this SQL:
select REPLACE(CONVERT(VARCHAR(24),GETDATE(),103),'/','_') + '_'+
REPLACE(CONVERT(VARCHAR(24),GETDATE(),114),':','_')
edited Jul 10 '17 at 8:54
Andre
19.5k41849
answered Jul 10 '17 at 6:29
DilkhushDilkhush
112
add a comment |
1
For SQL Server 2008+ You can use CONVERT and FORMAT together.
For example, for European style (e.g. Germany) timestamp:
CONVERT(VARCHAR, FORMAT(GETDATE(), 'dd.MM.yyyy HH:mm:ss', 'de-DE'))
answered Nov 26 '18 at 17:28
Peter MajkoPeter Majko
630515
add a comment |
0
You did not say which database, but with mysql here is an easy way to get a date from a timestamp (and the varchar type conversion should happen automatically):
mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2008-09-16 |
+-------------+
1 row in set (0.00 sec)
answered Sep 16 '08 at 16:49
Allan WindAllan Wind
2,10821621
Check again: he specified sql server via a tag.
– Joel Coehoorn
Sep 16 '08 at 16:51
add a comment |
0
The shortest and the simplest way is :
DECLARE @now AS DATETIME = GETDATE()
SELECT CONVERT(VARCHAR, @now, 23)
answered Dec 1 '14 at 15:44
KonstantinKonstantin
2,2221520
add a comment |
0
DECLARE @DateTime DATETIME
SET @DateTime = '2018-11-23 10:03:23'
SELECT CONVERT(VARCHAR(100),@DateTime,121 )
edited Nov 23 '18 at 4:50
K.Dᴀᴠɪs
7,189112439
answered Nov 23 '18 at 4:30
DilkhushDilkhush
112
add a comment |
-1
CONVERT(VARCHAR, GETDATE(), 23)
edited Apr 29 '14 at 16:47
Ashkan Mobayen Khiabani
20.3k1565115
answered Apr 29 '14 at 16:13
GabrielGabriel
372310
Doesn't work at all.
– Daria
Oct 8 '14 at 6:38
add a comment |
-3
You don't say what language but I am assuming C#/.NET because it has a native DateTime data type. In that case just convert it using the ToString method and use a format specifier such as:
DateTime d = DateTime.Today;
string result = d.ToString("yyyy-MM-dd");
However, I would caution against using this in a database query or concatenated into a SQL statement. Databases require a specific formatting string to be used. You are better off zeroing out the time part and using the DateTime as a SQL parameter if that is what you are trying to accomplish.
edited Nov 25 '15 at 11:14
Sabyasachi Mishra
1,2472038
answered Sep 16 '08 at 16:52
Johnny BravadoJohnny Bravado
972
In the question, it's mentioned "I am working on a query in Sql Server 2005".
– InkHeart
Aug 23 '16 at 1:04
4
@InkHeart and all the down-voters - This answer was posted in 2008. If you check the history of edits for the original question there were no tags and/or any other technology-related information included.
– GSazheniuk
Apr 6 '17 at 21:33
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});
}
});
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f74385%2fhow-to-convert-datetime-to-varchar%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
21 Answers
21
active
oldest
votes
21 Answers
21
active
oldest
votes
active
oldest
votes
active
oldest
votes
238
With Microsoft Sql Server:
--
-- Create test case
--
DECLARE @myDateTime DATETIME
SET @myDateTime = '2008-05-03'
--
-- Convert string
--
SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)
answered Sep 16 '08 at 16:52
TonyOssaTonyOssa
2,93711314
9
For those of you who may be interested, this would perform better if you change VARCHAR to CHAR. See this post (stackoverflow.com/questions/59667/…) for more details. Essentially, there are 2 bytes of overhead involved in VARCHAR vs CHAR. In this scenario, we know that your string will always be 10 characters, so CHAR is appropriate.
– Will Ediger
Dec 30 '14 at 23:04
Side note, from what I've seen, too bad none of the formats honors whatever the DATEFORMAT is set to, maybe there is another way.
– Alex Nolasco
May 15 '15 at 16:07
1
Is there any reason to useLEFT(.., 10)instead ofCONVERT(CHAR(10), ...)? Also those working with the newer versions of SQL Server than 2005(!) should check out the answer by Zar Shardan suggesting a solution based onFORMAT(date_value, format_string)function.
– Nickolay
Jan 25 '17 at 15:28
@Nickolay besidesFORMAT()is too slow relative toconvert(char(10),...)
– abdul qayyum
Nov 2 '17 at 10:08
add a comment |
238
With Microsoft Sql Server:
--
-- Create test case
--
DECLARE @myDateTime DATETIME
SET @myDateTime = '2008-05-03'
--
-- Convert string
--
SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)
answered Sep 16 '08 at 16:52
TonyOssaTonyOssa
2,93711314
9
For those of you who may be interested, this would perform better if you change VARCHAR to CHAR. See this post (stackoverflow.com/questions/59667/…) for more details. Essentially, there are 2 bytes of overhead involved in VARCHAR vs CHAR. In this scenario, we know that your string will always be 10 characters, so CHAR is appropriate.
– Will Ediger
Dec 30 '14 at 23:04
Side note, from what I've seen, too bad none of the formats honors whatever the DATEFORMAT is set to, maybe there is another way.
– Alex Nolasco
May 15 '15 at 16:07
1
Is there any reason to useLEFT(.., 10)instead ofCONVERT(CHAR(10), ...)? Also those working with the newer versions of SQL Server than 2005(!) should check out the answer by Zar Shardan suggesting a solution based onFORMAT(date_value, format_string)function.
– Nickolay
Jan 25 '17 at 15:28
@Nickolay besidesFORMAT()is too slow relative toconvert(char(10),...)
– abdul qayyum
Nov 2 '17 at 10:08
add a comment |
238
238
238
With Microsoft Sql Server:
--
-- Create test case
--
DECLARE @myDateTime DATETIME
SET @myDateTime = '2008-05-03'
--
-- Convert string
--
SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)
answered Sep 16 '08 at 16:52
TonyOssaTonyOssa
2,93711314
With Microsoft Sql Server:
--
-- Create test case
--
DECLARE @myDateTime DATETIME
SET @myDateTime = '2008-05-03'
--
-- Convert string
--
SELECT LEFT(CONVERT(VARCHAR, @myDateTime, 120), 10)
answered Sep 16 '08 at 16:52
TonyOssaTonyOssa
2,93711314
answered Sep 16 '08 at 16:52
TonyOssaTonyOssa
2,93711314
answered Sep 16 '08 at 16:52
TonyOssaTonyOssa
2,93711314
answered Sep 16 '08 at 16:52
TonyOssaTonyOssa
2,93711314
2,93711314
9
For those of you who may be interested, this would perform better if you change VARCHAR to CHAR. See this post (stackoverflow.com/questions/59667/…) for more details. Essentially, there are 2 bytes of overhead involved in VARCHAR vs CHAR. In this scenario, we know that your string will always be 10 characters, so CHAR is appropriate.
– Will Ediger
Dec 30 '14 at 23:04
Side note, from what I've seen, too bad none of the formats honors whatever the DATEFORMAT is set to, maybe there is another way.
– Alex Nolasco
May 15 '15 at 16:07
1
Is there any reason to useLEFT(.., 10)instead ofCONVERT(CHAR(10), ...)? Also those working with the newer versions of SQL Server than 2005(!) should check out the answer by Zar Shardan suggesting a solution based onFORMAT(date_value, format_string)function.
– Nickolay
Jan 25 '17 at 15:28
@Nickolay besidesFORMAT()is too slow relative toconvert(char(10),...)
– abdul qayyum
Nov 2 '17 at 10:08
add a comment |
9
For those of you who may be interested, this would perform better if you change VARCHAR to CHAR. See this post (stackoverflow.com/questions/59667/…) for more details. Essentially, there are 2 bytes of overhead involved in VARCHAR vs CHAR. In this scenario, we know that your string will always be 10 characters, so CHAR is appropriate.
– Will Ediger
Dec 30 '14 at 23:04
Side note, from what I've seen, too bad none of the formats honors whatever the DATEFORMAT is set to, maybe there is another way.
– Alex Nolasco
May 15 '15 at 16:07
1
Is there any reason to useLEFT(.., 10)instead ofCONVERT(CHAR(10), ...)? Also those working with the newer versions of SQL Server than 2005(!) should check out the answer by Zar Shardan suggesting a solution based onFORMAT(date_value, format_string)function.
– Nickolay
Jan 25 '17 at 15:28
@Nickolay besidesFORMAT()is too slow relative toconvert(char(10),...)
– abdul qayyum
Nov 2 '17 at 10:08
9
9
For those of you who may be interested, this would perform better if you change VARCHAR to CHAR. See this post (stackoverflow.com/questions/59667/…) for more details. Essentially, there are 2 bytes of overhead involved in VARCHAR vs CHAR. In this scenario, we know that your string will always be 10 characters, so CHAR is appropriate.
– Will Ediger
Dec 30 '14 at 23:04
For those of you who may be interested, this would perform better if you change VARCHAR to CHAR. See this post (stackoverflow.com/questions/59667/…) for more details. Essentially, there are 2 bytes of overhead involved in VARCHAR vs CHAR. In this scenario, we know that your string will always be 10 characters, so CHAR is appropriate.
– Will Ediger
Dec 30 '14 at 23:04
Side note, from what I've seen, too bad none of the formats honors whatever the DATEFORMAT is set to, maybe there is another way.
– Alex Nolasco
May 15 '15 at 16:07
Side note, from what I've seen, too bad none of the formats honors whatever the DATEFORMAT is set to, maybe there is another way.
– Alex Nolasco
May 15 '15 at 16:07
1
1
Is there any reason to use
LEFT(.., 10) instead of CONVERT(CHAR(10), ...)? Also those working with the newer versions of SQL Server than 2005(!) should check out the answer by Zar Shardan suggesting a solution based on FORMAT(date_value, format_string) function.– Nickolay
Jan 25 '17 at 15:28
Is there any reason to use
LEFT(.., 10) instead of CONVERT(CHAR(10), ...)? Also those working with the newer versions of SQL Server than 2005(!) should check out the answer by Zar Shardan suggesting a solution based on FORMAT(date_value, format_string) function.– Nickolay
Jan 25 '17 at 15:28
@Nickolay besides
FORMAT() is too slow relative to convert(char(10),...)– abdul qayyum
Nov 2 '17 at 10:08
@Nickolay besides
FORMAT() is too slow relative to convert(char(10),...)– abdul qayyum
Nov 2 '17 at 10:08
add a comment |
329
Here's some test sql for all the styles.
DECLARE @now datetime
SET @now = GETDATE()
select convert(nvarchar(MAX), @now, 0) as output, 0 as style
union select convert(nvarchar(MAX), @now, 1), 1
union select convert(nvarchar(MAX), @now, 2), 2
union select convert(nvarchar(MAX), @now, 3), 3
union select convert(nvarchar(MAX), @now, 4), 4
union select convert(nvarchar(MAX), @now, 5), 5
union select convert(nvarchar(MAX), @now, 6), 6
union select convert(nvarchar(MAX), @now, 7), 7
union select convert(nvarchar(MAX), @now, 8), 8
union select convert(nvarchar(MAX), @now, 9), 9
union select convert(nvarchar(MAX), @now, 10), 10
union select convert(nvarchar(MAX), @now, 11), 11
union select convert(nvarchar(MAX), @now, 12), 12
union select convert(nvarchar(MAX), @now, 13), 13
union select convert(nvarchar(MAX), @now, 14), 14
--15 to 19 not valid
union select convert(nvarchar(MAX), @now, 20), 20
union select convert(nvarchar(MAX), @now, 21), 21
union select convert(nvarchar(MAX), @now, 22), 22
union select convert(nvarchar(MAX), @now, 23), 23
union select convert(nvarchar(MAX), @now, 24), 24
union select convert(nvarchar(MAX), @now, 25), 25
--26 to 99 not valid
union select convert(nvarchar(MAX), @now, 100), 100
union select convert(nvarchar(MAX), @now, 101), 101
union select convert(nvarchar(MAX), @now, 102), 102
union select convert(nvarchar(MAX), @now, 103), 103
union select convert(nvarchar(MAX), @now, 104), 104
union select convert(nvarchar(MAX), @now, 105), 105
union select convert(nvarchar(MAX), @now, 106), 106
union select convert(nvarchar(MAX), @now, 107), 107
union select convert(nvarchar(MAX), @now, 108), 108
union select convert(nvarchar(MAX), @now, 109), 109
union select convert(nvarchar(MAX), @now, 110), 110
union select convert(nvarchar(MAX), @now, 111), 111
union select convert(nvarchar(MAX), @now, 112), 112
union select convert(nvarchar(MAX), @now, 113), 113
union select convert(nvarchar(MAX), @now, 114), 114
union select convert(nvarchar(MAX), @now, 120), 120
union select convert(nvarchar(MAX), @now, 121), 121
--122 to 125 not valid
union select convert(nvarchar(MAX), @now, 126), 126
union select convert(nvarchar(MAX), @now, 127), 127
--128, 129 not valid
union select convert(nvarchar(MAX), @now, 130), 130
union select convert(nvarchar(MAX), @now, 131), 131
--132 not valid
order BY style
Here's the result
output style
Apr 28 2014 9:31AM 0
04/28/14 1
14.04.28 2
28/04/14 3
28.04.14 4
28-04-14 5
28 Apr 14 6
Apr 28, 14 7
09:31:28 8
Apr 28 2014 9:31:28:580AM 9
04-28-14 10
14/04/28 11
140428 12
28 Apr 2014 09:31:28:580 13
09:31:28:580 14
2014-04-28 09:31:28 20
2014-04-28 09:31:28.580 21
04/28/14 9:31:28 AM 22
2014-04-28 23
09:31:28 24
2014-04-28 09:31:28.580 25
Apr 28 2014 9:31AM 100
04/28/2014 101
2014.04.28 102
28/04/2014 103
28.04.2014 104
28-04-2014 105
28 Apr 2014 106
Apr 28, 2014 107
09:31:28 108
Apr 28 2014 9:31:28:580AM 109
04-28-2014 110
2014/04/28 111
20140428 112
28 Apr 2014 09:31:28:580 113
09:31:28:580 114
2014-04-28 09:31:28 120
2014-04-28 09:31:28.580 121
2014-04-28T09:31:28.580 126
2014-04-28T09:31:28.580 127
28 جمادى الثانية 1435 9:31:28:580AM 130
28/06/1435 9:31:28:580AM 131
Make nvarchar(max) shorter to trim the time. For example:
select convert(nvarchar(11), GETDATE(), 0)
union select convert(nvarchar(max), GETDATE(), 0)
outputs:
May 18 2018
May 18 2018 9:57AM
answered Oct 23 '13 at 9:16
ColinColin
16.8k1583153
Here Sometimes we want likedd-mmorjun - 28. There is any option??
– Rock
May 24 '16 at 14:32
Works for SQL Server 2005 so this is great for those of us that are still unfortunately meddling with older systems. Upgrading to 2014 soon, excited!
– dyslexicanaboko
Aug 26 '16 at 17:38
I wanna format close to 101 -- 101 = 04/28/2014 , I wanna without zero in the month , output 4/28/2014, is it applicable ?
– ahmed abdelqader
Nov 10 '16 at 7:35
1
Isn't the timezone identifier Z missing in style 127.
– Atishay
Sep 1 '17 at 7:48
@Atishay "only supported when casting from character data to datetime or smalldatetime". See the footnotes 6 and 7 at docs.microsoft.com/de-de/sql/t-sql/functions/…
– Colin
Sep 4 '17 at 11:15
|
show 1 more comment
329
Here's some test sql for all the styles.
DECLARE @now datetime
SET @now = GETDATE()
select convert(nvarchar(MAX), @now, 0) as output, 0 as style
union select convert(nvarchar(MAX), @now, 1), 1
union select convert(nvarchar(MAX), @now, 2), 2
union select convert(nvarchar(MAX), @now, 3), 3
union select convert(nvarchar(MAX), @now, 4), 4
union select convert(nvarchar(MAX), @now, 5), 5
union select convert(nvarchar(MAX), @now, 6), 6
union select convert(nvarchar(MAX), @now, 7), 7
union select convert(nvarchar(MAX), @now, 8), 8
union select convert(nvarchar(MAX), @now, 9), 9
union select convert(nvarchar(MAX), @now, 10), 10
union select convert(nvarchar(MAX), @now, 11), 11
union select convert(nvarchar(MAX), @now, 12), 12
union select convert(nvarchar(MAX), @now, 13), 13
union select convert(nvarchar(MAX), @now, 14), 14
--15 to 19 not valid
union select convert(nvarchar(MAX), @now, 20), 20
union select convert(nvarchar(MAX), @now, 21), 21
union select convert(nvarchar(MAX), @now, 22), 22
union select convert(nvarchar(MAX), @now, 23), 23
union select convert(nvarchar(MAX), @now, 24), 24
union select convert(nvarchar(MAX), @now, 25), 25
--26 to 99 not valid
union select convert(nvarchar(MAX), @now, 100), 100
union select convert(nvarchar(MAX), @now, 101), 101
union select convert(nvarchar(MAX), @now, 102), 102
union select convert(nvarchar(MAX), @now, 103), 103
union select convert(nvarchar(MAX), @now, 104), 104
union select convert(nvarchar(MAX), @now, 105), 105
union select convert(nvarchar(MAX), @now, 106), 106
union select convert(nvarchar(MAX), @now, 107), 107
union select convert(nvarchar(MAX), @now, 108), 108
union select convert(nvarchar(MAX), @now, 109), 109
union select convert(nvarchar(MAX), @now, 110), 110
union select convert(nvarchar(MAX), @now, 111), 111
union select convert(nvarchar(MAX), @now, 112), 112
union select convert(nvarchar(MAX), @now, 113), 113
union select convert(nvarchar(MAX), @now, 114), 114
union select convert(nvarchar(MAX), @now, 120), 120
union select convert(nvarchar(MAX), @now, 121), 121
--122 to 125 not valid
union select convert(nvarchar(MAX), @now, 126), 126
union select convert(nvarchar(MAX), @now, 127), 127
--128, 129 not valid
union select convert(nvarchar(MAX), @now, 130), 130
union select convert(nvarchar(MAX), @now, 131), 131
--132 not valid
order BY style
Here's the result
output style
Apr 28 2014 9:31AM 0
04/28/14 1
14.04.28 2
28/04/14 3
28.04.14 4
28-04-14 5
28 Apr 14 6
Apr 28, 14 7
09:31:28 8
Apr 28 2014 9:31:28:580AM 9
04-28-14 10
14/04/28 11
140428 12
28 Apr 2014 09:31:28:580 13
09:31:28:580 14
2014-04-28 09:31:28 20
2014-04-28 09:31:28.580 21
04/28/14 9:31:28 AM 22
2014-04-28 23
09:31:28 24
2014-04-28 09:31:28.580 25
Apr 28 2014 9:31AM 100
04/28/2014 101
2014.04.28 102
28/04/2014 103
28.04.2014 104
28-04-2014 105
28 Apr 2014 106
Apr 28, 2014 107
09:31:28 108
Apr 28 2014 9:31:28:580AM 109
04-28-2014 110
2014/04/28 111
20140428 112
28 Apr 2014 09:31:28:580 113
09:31:28:580 114
2014-04-28 09:31:28 120
2014-04-28 09:31:28.580 121
2014-04-28T09:31:28.580 126
2014-04-28T09:31:28.580 127
28 جمادى الثانية 1435 9:31:28:580AM 130
28/06/1435 9:31:28:580AM 131
Make nvarchar(max) shorter to trim the time. For example:
select convert(nvarchar(11), GETDATE(), 0)
union select convert(nvarchar(max), GETDATE(), 0)
outputs:
May 18 2018
May 18 2018 9:57AM
answered Oct 23 '13 at 9:16
ColinColin
16.8k1583153
Here Sometimes we want likedd-mmorjun - 28. There is any option??
– Rock
May 24 '16 at 14:32
Works for SQL Server 2005 so this is great for those of us that are still unfortunately meddling with older systems. Upgrading to 2014 soon, excited!
– dyslexicanaboko
Aug 26 '16 at 17:38
I wanna format close to 101 -- 101 = 04/28/2014 , I wanna without zero in the month , output 4/28/2014, is it applicable ?
– ahmed abdelqader
Nov 10 '16 at 7:35
1
Isn't the timezone identifier Z missing in style 127.
– Atishay
Sep 1 '17 at 7:48
@Atishay "only supported when casting from character data to datetime or smalldatetime". See the footnotes 6 and 7 at docs.microsoft.com/de-de/sql/t-sql/functions/…
– Colin
Sep 4 '17 at 11:15
|
show 1 more comment
329
329
329
Here's some test sql for all the styles.
DECLARE @now datetime
SET @now = GETDATE()
select convert(nvarchar(MAX), @now, 0) as output, 0 as style
union select convert(nvarchar(MAX), @now, 1), 1
union select convert(nvarchar(MAX), @now, 2), 2
union select convert(nvarchar(MAX), @now, 3), 3
union select convert(nvarchar(MAX), @now, 4), 4
union select convert(nvarchar(MAX), @now, 5), 5
union select convert(nvarchar(MAX), @now, 6), 6
union select convert(nvarchar(MAX), @now, 7), 7
union select convert(nvarchar(MAX), @now, 8), 8
union select convert(nvarchar(MAX), @now, 9), 9
union select convert(nvarchar(MAX), @now, 10), 10
union select convert(nvarchar(MAX), @now, 11), 11
union select convert(nvarchar(MAX), @now, 12), 12
union select convert(nvarchar(MAX), @now, 13), 13
union select convert(nvarchar(MAX), @now, 14), 14
--15 to 19 not valid
union select convert(nvarchar(MAX), @now, 20), 20
union select convert(nvarchar(MAX), @now, 21), 21
union select convert(nvarchar(MAX), @now, 22), 22
union select convert(nvarchar(MAX), @now, 23), 23
union select convert(nvarchar(MAX), @now, 24), 24
union select convert(nvarchar(MAX), @now, 25), 25
--26 to 99 not valid
union select convert(nvarchar(MAX), @now, 100), 100
union select convert(nvarchar(MAX), @now, 101), 101
union select convert(nvarchar(MAX), @now, 102), 102
union select convert(nvarchar(MAX), @now, 103), 103
union select convert(nvarchar(MAX), @now, 104), 104
union select convert(nvarchar(MAX), @now, 105), 105
union select convert(nvarchar(MAX), @now, 106), 106
union select convert(nvarchar(MAX), @now, 107), 107
union select convert(nvarchar(MAX), @now, 108), 108
union select convert(nvarchar(MAX), @now, 109), 109
union select convert(nvarchar(MAX), @now, 110), 110
union select convert(nvarchar(MAX), @now, 111), 111
union select convert(nvarchar(MAX), @now, 112), 112
union select convert(nvarchar(MAX), @now, 113), 113
union select convert(nvarchar(MAX), @now, 114), 114
union select convert(nvarchar(MAX), @now, 120), 120
union select convert(nvarchar(MAX), @now, 121), 121
--122 to 125 not valid
union select convert(nvarchar(MAX), @now, 126), 126
union select convert(nvarchar(MAX), @now, 127), 127
--128, 129 not valid
union select convert(nvarchar(MAX), @now, 130), 130
union select convert(nvarchar(MAX), @now, 131), 131
--132 not valid
order BY style
Here's the result
output style
Apr 28 2014 9:31AM 0
04/28/14 1
14.04.28 2
28/04/14 3
28.04.14 4
28-04-14 5
28 Apr 14 6
Apr 28, 14 7
09:31:28 8
Apr 28 2014 9:31:28:580AM 9
04-28-14 10
14/04/28 11
140428 12
28 Apr 2014 09:31:28:580 13
09:31:28:580 14
2014-04-28 09:31:28 20
2014-04-28 09:31:28.580 21
04/28/14 9:31:28 AM 22
2014-04-28 23
09:31:28 24
2014-04-28 09:31:28.580 25
Apr 28 2014 9:31AM 100
04/28/2014 101
2014.04.28 102
28/04/2014 103
28.04.2014 104
28-04-2014 105
28 Apr 2014 106
Apr 28, 2014 107
09:31:28 108
Apr 28 2014 9:31:28:580AM 109
04-28-2014 110
2014/04/28 111
20140428 112
28 Apr 2014 09:31:28:580 113
09:31:28:580 114
2014-04-28 09:31:28 120
2014-04-28 09:31:28.580 121
2014-04-28T09:31:28.580 126
2014-04-28T09:31:28.580 127
28 جمادى الثانية 1435 9:31:28:580AM 130
28/06/1435 9:31:28:580AM 131
Make nvarchar(max) shorter to trim the time. For example:
select convert(nvarchar(11), GETDATE(), 0)
union select convert(nvarchar(max), GETDATE(), 0)
outputs:
May 18 2018
May 18 2018 9:57AM
answered Oct 23 '13 at 9:16
ColinColin
16.8k1583153
Here's some test sql for all the styles.
DECLARE @now datetime
SET @now = GETDATE()
select convert(nvarchar(MAX), @now, 0) as output, 0 as style
union select convert(nvarchar(MAX), @now, 1), 1
union select convert(nvarchar(MAX), @now, 2), 2
union select convert(nvarchar(MAX), @now, 3), 3
union select convert(nvarchar(MAX), @now, 4), 4
union select convert(nvarchar(MAX), @now, 5), 5
union select convert(nvarchar(MAX), @now, 6), 6
union select convert(nvarchar(MAX), @now, 7), 7
union select convert(nvarchar(MAX), @now, 8), 8
union select convert(nvarchar(MAX), @now, 9), 9
union select convert(nvarchar(MAX), @now, 10), 10
union select convert(nvarchar(MAX), @now, 11), 11
union select convert(nvarchar(MAX), @now, 12), 12
union select convert(nvarchar(MAX), @now, 13), 13
union select convert(nvarchar(MAX), @now, 14), 14
--15 to 19 not valid
union select convert(nvarchar(MAX), @now, 20), 20
union select convert(nvarchar(MAX), @now, 21), 21
union select convert(nvarchar(MAX), @now, 22), 22
union select convert(nvarchar(MAX), @now, 23), 23
union select convert(nvarchar(MAX), @now, 24), 24
union select convert(nvarchar(MAX), @now, 25), 25
--26 to 99 not valid
union select convert(nvarchar(MAX), @now, 100), 100
union select convert(nvarchar(MAX), @now, 101), 101
union select convert(nvarchar(MAX), @now, 102), 102
union select convert(nvarchar(MAX), @now, 103), 103
union select convert(nvarchar(MAX), @now, 104), 104
union select convert(nvarchar(MAX), @now, 105), 105
union select convert(nvarchar(MAX), @now, 106), 106
union select convert(nvarchar(MAX), @now, 107), 107
union select convert(nvarchar(MAX), @now, 108), 108
union select convert(nvarchar(MAX), @now, 109), 109
union select convert(nvarchar(MAX), @now, 110), 110
union select convert(nvarchar(MAX), @now, 111), 111
union select convert(nvarchar(MAX), @now, 112), 112
union select convert(nvarchar(MAX), @now, 113), 113
union select convert(nvarchar(MAX), @now, 114), 114
union select convert(nvarchar(MAX), @now, 120), 120
union select convert(nvarchar(MAX), @now, 121), 121
--122 to 125 not valid
union select convert(nvarchar(MAX), @now, 126), 126
union select convert(nvarchar(MAX), @now, 127), 127
--128, 129 not valid
union select convert(nvarchar(MAX), @now, 130), 130
union select convert(nvarchar(MAX), @now, 131), 131
--132 not valid
order BY style
Here's the result
output style
Apr 28 2014 9:31AM 0
04/28/14 1
14.04.28 2
28/04/14 3
28.04.14 4
28-04-14 5
28 Apr 14 6
Apr 28, 14 7
09:31:28 8
Apr 28 2014 9:31:28:580AM 9
04-28-14 10
14/04/28 11
140428 12
28 Apr 2014 09:31:28:580 13
09:31:28:580 14
2014-04-28 09:31:28 20
2014-04-28 09:31:28.580 21
04/28/14 9:31:28 AM 22
2014-04-28 23
09:31:28 24
2014-04-28 09:31:28.580 25
Apr 28 2014 9:31AM 100
04/28/2014 101
2014.04.28 102
28/04/2014 103
28.04.2014 104
28-04-2014 105
28 Apr 2014 106
Apr 28, 2014 107
09:31:28 108
Apr 28 2014 9:31:28:580AM 109
04-28-2014 110
2014/04/28 111
20140428 112
28 Apr 2014 09:31:28:580 113
09:31:28:580 114
2014-04-28 09:31:28 120
2014-04-28 09:31:28.580 121
2014-04-28T09:31:28.580 126
2014-04-28T09:31:28.580 127
28 جمادى الثانية 1435 9:31:28:580AM 130
28/06/1435 9:31:28:580AM 131
Make nvarchar(max) shorter to trim the time. For example:
select convert(nvarchar(11), GETDATE(), 0)
union select convert(nvarchar(max), GETDATE(), 0)
outputs:
May 18 2018
May 18 2018 9:57AM
answered Oct 23 '13 at 9:16
ColinColin
16.8k1583153
edited May 18 '18 at 9:59
answered Oct 23 '13 at 9:16
ColinColin
16.8k1583153
answered Oct 23 '13 at 9:16
ColinColin
16.8k1583153
answered Oct 23 '13 at 9:16
ColinColin
16.8k1583153
16.8k1583153
Here Sometimes we want likedd-mmorjun - 28. There is any option??
– Rock
May 24 '16 at 14:32
Works for SQL Server 2005 so this is great for those of us that are still unfortunately meddling with older systems. Upgrading to 2014 soon, excited!
– dyslexicanaboko
Aug 26 '16 at 17:38
I wanna format close to 101 -- 101 = 04/28/2014 , I wanna without zero in the month , output 4/28/2014, is it applicable ?
– ahmed abdelqader
Nov 10 '16 at 7:35
1
Isn't the timezone identifier Z missing in style 127.
– Atishay
Sep 1 '17 at 7:48
@Atishay "only supported when casting from character data to datetime or smalldatetime". See the footnotes 6 and 7 at docs.microsoft.com/de-de/sql/t-sql/functions/…
– Colin
Sep 4 '17 at 11:15
|
show 1 more comment
Here Sometimes we want likedd-mmorjun - 28. There is any option??
– Rock
May 24 '16 at 14:32
Works for SQL Server 2005 so this is great for those of us that are still unfortunately meddling with older systems. Upgrading to 2014 soon, excited!
– dyslexicanaboko
Aug 26 '16 at 17:38
I wanna format close to 101 -- 101 = 04/28/2014 , I wanna without zero in the month , output 4/28/2014, is it applicable ?
– ahmed abdelqader
Nov 10 '16 at 7:35
1
Isn't the timezone identifier Z missing in style 127.
– Atishay
Sep 1 '17 at 7:48
@Atishay "only supported when casting from character data to datetime or smalldatetime". See the footnotes 6 and 7 at docs.microsoft.com/de-de/sql/t-sql/functions/…
– Colin
Sep 4 '17 at 11:15
Here Sometimes we want like
dd-mm or jun - 28. There is any option??– Rock
May 24 '16 at 14:32
Here Sometimes we want like
dd-mm or jun - 28. There is any option??– Rock
May 24 '16 at 14:32
Works for SQL Server 2005 so this is great for those of us that are still unfortunately meddling with older systems. Upgrading to 2014 soon, excited!
– dyslexicanaboko
Aug 26 '16 at 17:38
Works for SQL Server 2005 so this is great for those of us that are still unfortunately meddling with older systems. Upgrading to 2014 soon, excited!
– dyslexicanaboko
Aug 26 '16 at 17:38
I wanna format close to 101 -- 101 = 04/28/2014 , I wanna without zero in the month , output 4/28/2014, is it applicable ?
– ahmed abdelqader
Nov 10 '16 at 7:35
I wanna format close to 101 -- 101 = 04/28/2014 , I wanna without zero in the month , output 4/28/2014, is it applicable ?
– ahmed abdelqader
Nov 10 '16 at 7:35
1
1
Isn't the timezone identifier Z missing in style 127.
– Atishay
Sep 1 '17 at 7:48
Isn't the timezone identifier Z missing in style 127.
– Atishay
Sep 1 '17 at 7:48
@Atishay "only supported when casting from character data to datetime or smalldatetime". See the footnotes 6 and 7 at docs.microsoft.com/de-de/sql/t-sql/functions/…
– Colin
Sep 4 '17 at 11:15
@Atishay "only supported when casting from character data to datetime or smalldatetime". See the footnotes 6 and 7 at docs.microsoft.com/de-de/sql/t-sql/functions/…
– Colin
Sep 4 '17 at 11:15
|
show 1 more comment
179
Try the following:
CONVERT(varchar(10), [MyDateTimecolumn], 20)
For a full date time and not just date do:
CONVERT(varchar(23), [MyDateTimecolumn], 121)
See this page for convert styles:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
OR
SQL Server CONVERT() Function
edited May 31 '15 at 13:09
Yakir Manor
3,77312123
answered Sep 16 '08 at 16:45
Joel CoehoornJoel Coehoorn
308k95490721
add a comment |
179
Try the following:
CONVERT(varchar(10), [MyDateTimecolumn], 20)
For a full date time and not just date do:
CONVERT(varchar(23), [MyDateTimecolumn], 121)
See this page for convert styles:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
OR
SQL Server CONVERT() Function
edited May 31 '15 at 13:09
Yakir Manor
3,77312123
answered Sep 16 '08 at 16:45
Joel CoehoornJoel Coehoorn
308k95490721
add a comment |
179
179
179
Try the following:
CONVERT(varchar(10), [MyDateTimecolumn], 20)
For a full date time and not just date do:
CONVERT(varchar(23), [MyDateTimecolumn], 121)
See this page for convert styles:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
OR
SQL Server CONVERT() Function
edited May 31 '15 at 13:09
Yakir Manor
3,77312123
answered Sep 16 '08 at 16:45
Joel CoehoornJoel Coehoorn
308k95490721
Try the following:
CONVERT(varchar(10), [MyDateTimecolumn], 20)
For a full date time and not just date do:
CONVERT(varchar(23), [MyDateTimecolumn], 121)
See this page for convert styles:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
OR
SQL Server CONVERT() Function
edited May 31 '15 at 13:09
Yakir Manor
3,77312123
answered Sep 16 '08 at 16:45
Joel CoehoornJoel Coehoorn
308k95490721
edited May 31 '15 at 13:09
Yakir Manor
3,77312123
edited May 31 '15 at 13:09
Yakir Manor
3,77312123
edited May 31 '15 at 13:09
Yakir Manor
3,77312123
3,77312123
answered Sep 16 '08 at 16:45
Joel CoehoornJoel Coehoorn
308k95490721
answered Sep 16 '08 at 16:45
Joel CoehoornJoel Coehoorn
308k95490721
answered Sep 16 '08 at 16:45
Joel CoehoornJoel Coehoorn
308k95490721
308k95490721
add a comment |
add a comment |
27
SQL Server 2012 has a new function , FORMAT:
http://msdn.microsoft.com/en-us/library/ee634924.aspx
and you can use custom date time format strings: http://msdn.microsoft.com/en-us/library/ee634398.aspx
These pages imply it is also available on SQL2008R2, but I don't have one handy to test if that's the case.
Example usage (Australian datetime):
FORMAT(VALUE,'dd/MM/yyyy h:mm:ss tt')
answered Jul 18 '13 at 2:58
Zar ShardanZar Shardan
4,10112731
add a comment |
27
SQL Server 2012 has a new function , FORMAT:
http://msdn.microsoft.com/en-us/library/ee634924.aspx
and you can use custom date time format strings: http://msdn.microsoft.com/en-us/library/ee634398.aspx
These pages imply it is also available on SQL2008R2, but I don't have one handy to test if that's the case.
Example usage (Australian datetime):
FORMAT(VALUE,'dd/MM/yyyy h:mm:ss tt')
answered Jul 18 '13 at 2:58
Zar ShardanZar Shardan
4,10112731
add a comment |
27
27
27
SQL Server 2012 has a new function , FORMAT:
http://msdn.microsoft.com/en-us/library/ee634924.aspx
and you can use custom date time format strings: http://msdn.microsoft.com/en-us/library/ee634398.aspx
These pages imply it is also available on SQL2008R2, but I don't have one handy to test if that's the case.
Example usage (Australian datetime):
FORMAT(VALUE,'dd/MM/yyyy h:mm:ss tt')
answered Jul 18 '13 at 2:58
Zar ShardanZar Shardan
4,10112731
SQL Server 2012 has a new function , FORMAT:
http://msdn.microsoft.com/en-us/library/ee634924.aspx
and you can use custom date time format strings: http://msdn.microsoft.com/en-us/library/ee634398.aspx
These pages imply it is also available on SQL2008R2, but I don't have one handy to test if that's the case.
Example usage (Australian datetime):
FORMAT(VALUE,'dd/MM/yyyy h:mm:ss tt')
answered Jul 18 '13 at 2:58
Zar ShardanZar Shardan
4,10112731
answered Jul 18 '13 at 2:58
Zar ShardanZar Shardan
4,10112731
answered Jul 18 '13 at 2:58
Zar ShardanZar Shardan
4,10112731
answered Jul 18 '13 at 2:58
Zar ShardanZar Shardan
4,10112731
4,10112731
add a comment |
add a comment |
9
Either Cast or Convert:
Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Actually since you asked for a specific format:
REPLACE(CONVERT(varchar(10), Date, 102), '.', '-')
edited May 11 '11 at 22:34
BlueRaja - Danny Pflughoeft
58k21152241
answered Sep 16 '08 at 16:46
Per Hornshøj-SchierbeckPer Hornshøj-Schierbeck
8,668177094
add a comment |
9
Either Cast or Convert:
Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Actually since you asked for a specific format:
REPLACE(CONVERT(varchar(10), Date, 102), '.', '-')
edited May 11 '11 at 22:34
BlueRaja - Danny Pflughoeft
58k21152241
answered Sep 16 '08 at 16:46
Per Hornshøj-SchierbeckPer Hornshøj-Schierbeck
8,668177094
add a comment |
9
9
9
Either Cast or Convert:
Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Actually since you asked for a specific format:
REPLACE(CONVERT(varchar(10), Date, 102), '.', '-')
edited May 11 '11 at 22:34
BlueRaja - Danny Pflughoeft
58k21152241
answered Sep 16 '08 at 16:46
Per Hornshøj-SchierbeckPer Hornshøj-Schierbeck
8,668177094
Either Cast or Convert:
Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Actually since you asked for a specific format:
REPLACE(CONVERT(varchar(10), Date, 102), '.', '-')
edited May 11 '11 at 22:34
BlueRaja - Danny Pflughoeft
58k21152241
answered Sep 16 '08 at 16:46
Per Hornshøj-SchierbeckPer Hornshøj-Schierbeck
8,668177094
edited May 11 '11 at 22:34
BlueRaja - Danny Pflughoeft
58k21152241
edited May 11 '11 at 22:34
BlueRaja - Danny Pflughoeft
58k21152241
edited May 11 '11 at 22:34
BlueRaja - Danny Pflughoeft
58k21152241
58k21152241
answered Sep 16 '08 at 16:46
Per Hornshøj-SchierbeckPer Hornshøj-Schierbeck
8,668177094
answered Sep 16 '08 at 16:46
Per Hornshøj-SchierbeckPer Hornshøj-Schierbeck
8,668177094
answered Sep 16 '08 at 16:46
Per Hornshøj-SchierbeckPer Hornshøj-Schierbeck
8,668177094
8,668177094
add a comment |
add a comment |
8
You can use DATEPART(DATEPART, VARIABLE). For example:
DECLARE @DAY INT
DECLARE @MONTH INT
DECLARE @YEAR INT
DECLARE @DATE DATETIME
@DATE = GETDATE()
SELECT @DAY = DATEPART(DAY,@DATE)
SELECT @MONTH = DATEPART(MONTH,@DATE)
SELECT @YEAR = DATEPART(YEAR,@DATE)
edited Jul 20 '12 at 21:47
kay
18.3k968115
answered Jul 20 '12 at 21:42
FCKOEFCKOE
8111
add a comment |
8
You can use DATEPART(DATEPART, VARIABLE). For example:
DECLARE @DAY INT
DECLARE @MONTH INT
DECLARE @YEAR INT
DECLARE @DATE DATETIME
@DATE = GETDATE()
SELECT @DAY = DATEPART(DAY,@DATE)
SELECT @MONTH = DATEPART(MONTH,@DATE)
SELECT @YEAR = DATEPART(YEAR,@DATE)
edited Jul 20 '12 at 21:47
kay
18.3k968115
answered Jul 20 '12 at 21:42
FCKOEFCKOE
8111
add a comment |
8
8
8
You can use DATEPART(DATEPART, VARIABLE). For example:
DECLARE @DAY INT
DECLARE @MONTH INT
DECLARE @YEAR INT
DECLARE @DATE DATETIME
@DATE = GETDATE()
SELECT @DAY = DATEPART(DAY,@DATE)
SELECT @MONTH = DATEPART(MONTH,@DATE)
SELECT @YEAR = DATEPART(YEAR,@DATE)
edited Jul 20 '12 at 21:47
kay
18.3k968115
answered Jul 20 '12 at 21:42
FCKOEFCKOE
8111
You can use DATEPART(DATEPART, VARIABLE). For example:
DECLARE @DAY INT
DECLARE @MONTH INT
DECLARE @YEAR INT
DECLARE @DATE DATETIME
@DATE = GETDATE()
SELECT @DAY = DATEPART(DAY,@DATE)
SELECT @MONTH = DATEPART(MONTH,@DATE)
SELECT @YEAR = DATEPART(YEAR,@DATE)
edited Jul 20 '12 at 21:47
kay
18.3k968115
answered Jul 20 '12 at 21:42
FCKOEFCKOE
8111
edited Jul 20 '12 at 21:47
kay
18.3k968115
edited Jul 20 '12 at 21:47
kay
18.3k968115
edited Jul 20 '12 at 21:47
kay
18.3k968115
18.3k968115
answered Jul 20 '12 at 21:42
FCKOEFCKOE
8111
answered Jul 20 '12 at 21:42
FCKOEFCKOE
8111
answered Jul 20 '12 at 21:42
FCKOEFCKOE
8111
8111
add a comment |
add a comment |
5
-- This gives you the time as 0 in format 'yyyy-mm-dd 00:00:00.000'
SELECT CAST( CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME) ;
answered Jun 21 '11 at 18:45
P's-SQLP's-SQL
5112
add a comment |
5
-- This gives you the time as 0 in format 'yyyy-mm-dd 00:00:00.000'
SELECT CAST( CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME) ;
answered Jun 21 '11 at 18:45
P's-SQLP's-SQL
5112
add a comment |
5
5
5
-- This gives you the time as 0 in format 'yyyy-mm-dd 00:00:00.000'
SELECT CAST( CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME) ;
answered Jun 21 '11 at 18:45
P's-SQLP's-SQL
5112
-- This gives you the time as 0 in format 'yyyy-mm-dd 00:00:00.000'
SELECT CAST( CONVERT(VARCHAR, GETDATE(), 101) AS DATETIME) ;
answered Jun 21 '11 at 18:45
P's-SQLP's-SQL
5112
edited Jun 21 '11 at 19:04
answered Jun 21 '11 at 18:45
P's-SQLP's-SQL
5112
answered Jun 21 '11 at 18:45
P's-SQLP's-SQL
5112
answered Jun 21 '11 at 18:45
P's-SQLP's-SQL
5112
5112
add a comment |
add a comment |
4
With Microsoft SQL Server:
Use Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Example:
SELECT CONVERT(varchar,d.dateValue,1-9)
For the style you can find more info here: MSDN - Cast and Convert (Transact-SQL).
edited Oct 20 '12 at 8:51
Pete Carter
2,44721833
answered May 30 '12 at 15:44
dmunozpadmunozpa
967
add a comment |
4
With Microsoft SQL Server:
Use Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Example:
SELECT CONVERT(varchar,d.dateValue,1-9)
For the style you can find more info here: MSDN - Cast and Convert (Transact-SQL).
edited Oct 20 '12 at 8:51
Pete Carter
2,44721833
answered May 30 '12 at 15:44
dmunozpadmunozpa
967
add a comment |
4
4
4
With Microsoft SQL Server:
Use Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Example:
SELECT CONVERT(varchar,d.dateValue,1-9)
For the style you can find more info here: MSDN - Cast and Convert (Transact-SQL).
edited Oct 20 '12 at 8:51
Pete Carter
2,44721833
answered May 30 '12 at 15:44
dmunozpadmunozpa
967
With Microsoft SQL Server:
Use Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Example:
SELECT CONVERT(varchar,d.dateValue,1-9)
For the style you can find more info here: MSDN - Cast and Convert (Transact-SQL).
edited Oct 20 '12 at 8:51
Pete Carter
2,44721833
answered May 30 '12 at 15:44
dmunozpadmunozpa
967
edited Oct 20 '12 at 8:51
Pete Carter
2,44721833
edited Oct 20 '12 at 8:51
Pete Carter
2,44721833
edited Oct 20 '12 at 8:51
Pete Carter
2,44721833
2,44721833
answered May 30 '12 at 15:44
dmunozpadmunozpa
967
answered May 30 '12 at 15:44
dmunozpadmunozpa
967
answered May 30 '12 at 15:44
dmunozpadmunozpa
967
967
add a comment |
add a comment |
2
Try:
select replace(convert(varchar, getdate(), 111),'/','-');
More on ms sql tips
answered Jun 6 '11 at 15:32
Arek BeeArek Bee
15627
add a comment |
2
Try:
select replace(convert(varchar, getdate(), 111),'/','-');
More on ms sql tips
answered Jun 6 '11 at 15:32
Arek BeeArek Bee
15627
add a comment |
2
2
2
Try:
select replace(convert(varchar, getdate(), 111),'/','-');
More on ms sql tips
answered Jun 6 '11 at 15:32
Arek BeeArek Bee
15627
Try:
select replace(convert(varchar, getdate(), 111),'/','-');
More on ms sql tips
answered Jun 6 '11 at 15:32
Arek BeeArek Bee
15627
answered Jun 6 '11 at 15:32
Arek BeeArek Bee
15627
answered Jun 6 '11 at 15:32
Arek BeeArek Bee
15627
answered Jun 6 '11 at 15:32
Arek BeeArek Bee
15627
15627
add a comment |
add a comment |
2
Try the following:
CONVERT(VARCHAR(10),GetDate(),102)
Then you would need to replace the "." with "-".
Here is a site that helps
http://www.mssqltips.com/tip.asp?tip=1145
edited Apr 14 '14 at 10:54
DMK
2,03111733
answered Sep 16 '08 at 16:50
Amy PattersonAmy Patterson
3241624
add a comment |
2
Try the following:
CONVERT(VARCHAR(10),GetDate(),102)
Then you would need to replace the "." with "-".
Here is a site that helps
http://www.mssqltips.com/tip.asp?tip=1145
edited Apr 14 '14 at 10:54
DMK
2,03111733
answered Sep 16 '08 at 16:50
Amy PattersonAmy Patterson
3241624
add a comment |
2
2
2
Try the following:
CONVERT(VARCHAR(10),GetDate(),102)
Then you would need to replace the "." with "-".
Here is a site that helps
http://www.mssqltips.com/tip.asp?tip=1145
edited Apr 14 '14 at 10:54
DMK
2,03111733
answered Sep 16 '08 at 16:50
Amy PattersonAmy Patterson
3241624
Try the following:
CONVERT(VARCHAR(10),GetDate(),102)
Then you would need to replace the "." with "-".
Here is a site that helps
http://www.mssqltips.com/tip.asp?tip=1145
edited Apr 14 '14 at 10:54
DMK
2,03111733
answered Sep 16 '08 at 16:50
Amy PattersonAmy Patterson
3241624
edited Apr 14 '14 at 10:54
DMK
2,03111733
edited Apr 14 '14 at 10:54
DMK
2,03111733
edited Apr 14 '14 at 10:54
DMK
2,03111733
2,03111733
answered Sep 16 '08 at 16:50
Amy PattersonAmy Patterson
3241624
answered Sep 16 '08 at 16:50
Amy PattersonAmy Patterson
3241624
answered Sep 16 '08 at 16:50
Amy PattersonAmy Patterson
3241624
3241624
add a comment |
add a comment |
2
declare @dt datetime
set @dt = getdate()
select convert(char(10),@dt,120)
I have fixed data length of char(10) as you want a specific string format.
edited Apr 22 '14 at 7:22
Dawson Loudon
5,85122229
answered Sep 26 '08 at 9:33
Andy JonesAndy Jones
1,255813
add a comment |
2
declare @dt datetime
set @dt = getdate()
select convert(char(10),@dt,120)
I have fixed data length of char(10) as you want a specific string format.
edited Apr 22 '14 at 7:22
Dawson Loudon
5,85122229
answered Sep 26 '08 at 9:33
Andy JonesAndy Jones
1,255813
add a comment |
2
2
2
declare @dt datetime
set @dt = getdate()
select convert(char(10),@dt,120)
I have fixed data length of char(10) as you want a specific string format.
edited Apr 22 '14 at 7:22
Dawson Loudon
5,85122229
answered Sep 26 '08 at 9:33
Andy JonesAndy Jones
1,255813
declare @dt datetime
set @dt = getdate()
select convert(char(10),@dt,120)
I have fixed data length of char(10) as you want a specific string format.
edited Apr 22 '14 at 7:22
Dawson Loudon
5,85122229
answered Sep 26 '08 at 9:33
Andy JonesAndy Jones
1,255813
edited Apr 22 '14 at 7:22
Dawson Loudon
5,85122229
edited Apr 22 '14 at 7:22
Dawson Loudon
5,85122229
edited Apr 22 '14 at 7:22
Dawson Loudon
5,85122229
5,85122229
answered Sep 26 '08 at 9:33
Andy JonesAndy Jones
1,255813
answered Sep 26 '08 at 9:33
Andy JonesAndy Jones
1,255813
answered Sep 26 '08 at 9:33
Andy JonesAndy Jones
1,255813
1,255813
add a comment |
add a comment |
1
The OP mentioned datetime format. For me, the time part gets in the way.
I think it's a bit cleaner to remove the time portion (by casting datetime to date) before formatting.
convert( varchar(10), convert( date, @yourDate ) , 111 )
answered Aug 12 '11 at 13:38
m42m42
2,07242231
add a comment |
1
The OP mentioned datetime format. For me, the time part gets in the way.
I think it's a bit cleaner to remove the time portion (by casting datetime to date) before formatting.
convert( varchar(10), convert( date, @yourDate ) , 111 )
answered Aug 12 '11 at 13:38
m42m42
2,07242231
add a comment |
1
1
1
The OP mentioned datetime format. For me, the time part gets in the way.
I think it's a bit cleaner to remove the time portion (by casting datetime to date) before formatting.
convert( varchar(10), convert( date, @yourDate ) , 111 )
answered Aug 12 '11 at 13:38
m42m42
2,07242231
The OP mentioned datetime format. For me, the time part gets in the way.
I think it's a bit cleaner to remove the time portion (by casting datetime to date) before formatting.
convert( varchar(10), convert( date, @yourDate ) , 111 )
answered Aug 12 '11 at 13:38
m42m42
2,07242231
answered Aug 12 '11 at 13:38
m42m42
2,07242231
answered Aug 12 '11 at 13:38
m42m42
2,07242231
answered Aug 12 '11 at 13:38
m42m42
2,07242231
2,07242231
add a comment |
add a comment |
1
This is how I do it: CONVERT(NVARCHAR(10), DATE1, 103) )
edited Aug 11 '13 at 20:50
FabianCook
11.9k144996
answered Mar 25 '13 at 17:31
IvanSnekIvanSnek
8614
add a comment |
1
This is how I do it: CONVERT(NVARCHAR(10), DATE1, 103) )
edited Aug 11 '13 at 20:50
FabianCook
11.9k144996
answered Mar 25 '13 at 17:31
IvanSnekIvanSnek
8614
add a comment |
1
1
1
This is how I do it: CONVERT(NVARCHAR(10), DATE1, 103) )
edited Aug 11 '13 at 20:50
FabianCook
11.9k144996
answered Mar 25 '13 at 17:31
IvanSnekIvanSnek
8614
This is how I do it: CONVERT(NVARCHAR(10), DATE1, 103) )
edited Aug 11 '13 at 20:50
FabianCook
11.9k144996
answered Mar 25 '13 at 17:31
IvanSnekIvanSnek
8614
edited Aug 11 '13 at 20:50
FabianCook
11.9k144996
edited Aug 11 '13 at 20:50
FabianCook
11.9k144996
edited Aug 11 '13 at 20:50
FabianCook
11.9k144996
11.9k144996
answered Mar 25 '13 at 17:31
IvanSnekIvanSnek
8614
answered Mar 25 '13 at 17:31
IvanSnekIvanSnek
8614
answered Mar 25 '13 at 17:31
IvanSnekIvanSnek
8614
8614
add a comment |
add a comment |
1
You can convert your date in many formats, the syntaxe is simple to use :
CONVERT('TheTypeYouWant', 'TheDateToConvert', 'TheCodeForFormating' * )
CONVERT(NVARCHAR(10), DATE_OF_DAY, 103) => 15/09/2016
- The code is an integer, here 3 is the third formating without century, if you want the century just change the code to 103.
In your case, i've just converted and restrict size by nvarchar(10) like this :
CONVERT(NVARCHAR(10), MY_DATE_TIME, 120) => 2016-09-15
See more at : http://www.w3schools.com/sql/func_convert.asp
Another solution (if your date is a Datetime) is a simple CAST :
CAST(MY_DATE_TIME as DATE) => 2016-09-15
answered Jan 11 '17 at 15:37
Ema.HEma.H
1,81732032
add a comment |
1
You can convert your date in many formats, the syntaxe is simple to use :
CONVERT('TheTypeYouWant', 'TheDateToConvert', 'TheCodeForFormating' * )
CONVERT(NVARCHAR(10), DATE_OF_DAY, 103) => 15/09/2016
- The code is an integer, here 3 is the third formating without century, if you want the century just change the code to 103.
In your case, i've just converted and restrict size by nvarchar(10) like this :
CONVERT(NVARCHAR(10), MY_DATE_TIME, 120) => 2016-09-15
See more at : http://www.w3schools.com/sql/func_convert.asp
Another solution (if your date is a Datetime) is a simple CAST :
CAST(MY_DATE_TIME as DATE) => 2016-09-15
answered Jan 11 '17 at 15:37
Ema.HEma.H
1,81732032
add a comment |
1
1
1
You can convert your date in many formats, the syntaxe is simple to use :
CONVERT('TheTypeYouWant', 'TheDateToConvert', 'TheCodeForFormating' * )
CONVERT(NVARCHAR(10), DATE_OF_DAY, 103) => 15/09/2016
- The code is an integer, here 3 is the third formating without century, if you want the century just change the code to 103.
In your case, i've just converted and restrict size by nvarchar(10) like this :
CONVERT(NVARCHAR(10), MY_DATE_TIME, 120) => 2016-09-15
See more at : http://www.w3schools.com/sql/func_convert.asp
Another solution (if your date is a Datetime) is a simple CAST :
CAST(MY_DATE_TIME as DATE) => 2016-09-15
answered Jan 11 '17 at 15:37
Ema.HEma.H
1,81732032
You can convert your date in many formats, the syntaxe is simple to use :
CONVERT('TheTypeYouWant', 'TheDateToConvert', 'TheCodeForFormating' * )
CONVERT(NVARCHAR(10), DATE_OF_DAY, 103) => 15/09/2016
- The code is an integer, here 3 is the third formating without century, if you want the century just change the code to 103.
In your case, i've just converted and restrict size by nvarchar(10) like this :
CONVERT(NVARCHAR(10), MY_DATE_TIME, 120) => 2016-09-15
See more at : http://www.w3schools.com/sql/func_convert.asp
Another solution (if your date is a Datetime) is a simple CAST :
CAST(MY_DATE_TIME as DATE) => 2016-09-15
answered Jan 11 '17 at 15:37
Ema.HEma.H
1,81732032
edited Jan 11 '17 at 15:43
answered Jan 11 '17 at 15:37
Ema.HEma.H
1,81732032
answered Jan 11 '17 at 15:37
Ema.HEma.H
1,81732032
answered Jan 11 '17 at 15:37
Ema.HEma.H
1,81732032
1,81732032
add a comment |
add a comment |
1
Try this SQL:
select REPLACE(CONVERT(VARCHAR(24),GETDATE(),103),'/','_') + '_'+
REPLACE(CONVERT(VARCHAR(24),GETDATE(),114),':','_')
edited Jul 10 '17 at 8:54
Andre
19.5k41849
answered Jul 10 '17 at 6:29
DilkhushDilkhush
112
add a comment |
1
Try this SQL:
select REPLACE(CONVERT(VARCHAR(24),GETDATE(),103),'/','_') + '_'+
REPLACE(CONVERT(VARCHAR(24),GETDATE(),114),':','_')
edited Jul 10 '17 at 8:54
Andre
19.5k41849
answered Jul 10 '17 at 6:29
DilkhushDilkhush
112
add a comment |
1
1
1
Try this SQL:
select REPLACE(CONVERT(VARCHAR(24),GETDATE(),103),'/','_') + '_'+
REPLACE(CONVERT(VARCHAR(24),GETDATE(),114),':','_')
edited Jul 10 '17 at 8:54
Andre
19.5k41849
answered Jul 10 '17 at 6:29
DilkhushDilkhush
112
Try this SQL:
select REPLACE(CONVERT(VARCHAR(24),GETDATE(),103),'/','_') + '_'+
REPLACE(CONVERT(VARCHAR(24),GETDATE(),114),':','_')
edited Jul 10 '17 at 8:54
Andre
19.5k41849
answered Jul 10 '17 at 6:29
DilkhushDilkhush
112
edited Jul 10 '17 at 8:54
Andre
19.5k41849
edited Jul 10 '17 at 8:54
Andre
19.5k41849
edited Jul 10 '17 at 8:54
Andre
19.5k41849
19.5k41849
answered Jul 10 '17 at 6:29
DilkhushDilkhush
112
answered Jul 10 '17 at 6:29
DilkhushDilkhush
112
answered Jul 10 '17 at 6:29
DilkhushDilkhush
112
112
add a comment |
add a comment |
1
For SQL Server 2008+ You can use CONVERT and FORMAT together.
For example, for European style (e.g. Germany) timestamp:
CONVERT(VARCHAR, FORMAT(GETDATE(), 'dd.MM.yyyy HH:mm:ss', 'de-DE'))
answered Nov 26 '18 at 17:28
Peter MajkoPeter Majko
630515
add a comment |
1
For SQL Server 2008+ You can use CONVERT and FORMAT together.
For example, for European style (e.g. Germany) timestamp:
CONVERT(VARCHAR, FORMAT(GETDATE(), 'dd.MM.yyyy HH:mm:ss', 'de-DE'))
answered Nov 26 '18 at 17:28
Peter MajkoPeter Majko
630515
add a comment |
1
1
1
For SQL Server 2008+ You can use CONVERT and FORMAT together.
For example, for European style (e.g. Germany) timestamp:
CONVERT(VARCHAR, FORMAT(GETDATE(), 'dd.MM.yyyy HH:mm:ss', 'de-DE'))
answered Nov 26 '18 at 17:28
Peter MajkoPeter Majko
630515
For SQL Server 2008+ You can use CONVERT and FORMAT together.
For example, for European style (e.g. Germany) timestamp:
CONVERT(VARCHAR, FORMAT(GETDATE(), 'dd.MM.yyyy HH:mm:ss', 'de-DE'))
answered Nov 26 '18 at 17:28
Peter MajkoPeter Majko
630515
edited Nov 26 '18 at 17:54
answered Nov 26 '18 at 17:28
Peter MajkoPeter Majko
630515
answered Nov 26 '18 at 17:28
Peter MajkoPeter Majko
630515
answered Nov 26 '18 at 17:28
Peter MajkoPeter Majko
630515
630515
add a comment |
add a comment |
0
You did not say which database, but with mysql here is an easy way to get a date from a timestamp (and the varchar type conversion should happen automatically):
mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2008-09-16 |
+-------------+
1 row in set (0.00 sec)
answered Sep 16 '08 at 16:49
Allan WindAllan Wind
2,10821621
Check again: he specified sql server via a tag.
– Joel Coehoorn
Sep 16 '08 at 16:51
add a comment |
0
You did not say which database, but with mysql here is an easy way to get a date from a timestamp (and the varchar type conversion should happen automatically):
mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2008-09-16 |
+-------------+
1 row in set (0.00 sec)
answered Sep 16 '08 at 16:49
Allan WindAllan Wind
2,10821621
Check again: he specified sql server via a tag.
– Joel Coehoorn
Sep 16 '08 at 16:51
add a comment |
0
0
0
You did not say which database, but with mysql here is an easy way to get a date from a timestamp (and the varchar type conversion should happen automatically):
mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2008-09-16 |
+-------------+
1 row in set (0.00 sec)
answered Sep 16 '08 at 16:49
Allan WindAllan Wind
2,10821621
You did not say which database, but with mysql here is an easy way to get a date from a timestamp (and the varchar type conversion should happen automatically):
mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2008-09-16 |
+-------------+
1 row in set (0.00 sec)
answered Sep 16 '08 at 16:49
Allan WindAllan Wind
2,10821621
answered Sep 16 '08 at 16:49
Allan WindAllan Wind
2,10821621
answered Sep 16 '08 at 16:49
Allan WindAllan Wind
2,10821621
answered Sep 16 '08 at 16:49
Allan WindAllan Wind
2,10821621
2,10821621
Check again: he specified sql server via a tag.
– Joel Coehoorn
Sep 16 '08 at 16:51
add a comment |
Check again: he specified sql server via a tag.
– Joel Coehoorn
Sep 16 '08 at 16:51
Check again: he specified sql server via a tag.
– Joel Coehoorn
Sep 16 '08 at 16:51
Check again: he specified sql server via a tag.
– Joel Coehoorn
Sep 16 '08 at 16:51
add a comment |
0
The shortest and the simplest way is :
DECLARE @now AS DATETIME = GETDATE()
SELECT CONVERT(VARCHAR, @now, 23)
answered Dec 1 '14 at 15:44
KonstantinKonstantin
2,2221520
add a comment |
0
The shortest and the simplest way is :
DECLARE @now AS DATETIME = GETDATE()
SELECT CONVERT(VARCHAR, @now, 23)
answered Dec 1 '14 at 15:44
KonstantinKonstantin
2,2221520
add a comment |
0
0
0
The shortest and the simplest way is :
DECLARE @now AS DATETIME = GETDATE()
SELECT CONVERT(VARCHAR, @now, 23)
answered Dec 1 '14 at 15:44
KonstantinKonstantin
2,2221520
The shortest and the simplest way is :
DECLARE @now AS DATETIME = GETDATE()
SELECT CONVERT(VARCHAR, @now, 23)
answered Dec 1 '14 at 15:44
KonstantinKonstantin
2,2221520
answered Dec 1 '14 at 15:44
KonstantinKonstantin
2,2221520
answered Dec 1 '14 at 15:44
KonstantinKonstantin
2,2221520
answered Dec 1 '14 at 15:44
KonstantinKonstantin
2,2221520
2,2221520
add a comment |
add a comment |
0
DECLARE @DateTime DATETIME
SET @DateTime = '2018-11-23 10:03:23'
SELECT CONVERT(VARCHAR(100),@DateTime,121 )
edited Nov 23 '18 at 4:50
K.Dᴀᴠɪs
7,189112439
answered Nov 23 '18 at 4:30
DilkhushDilkhush
112
add a comment |
0
DECLARE @DateTime DATETIME
SET @DateTime = '2018-11-23 10:03:23'
SELECT CONVERT(VARCHAR(100),@DateTime,121 )
edited Nov 23 '18 at 4:50
K.Dᴀᴠɪs
7,189112439
answered Nov 23 '18 at 4:30
DilkhushDilkhush
112
add a comment |
0
0
0
DECLARE @DateTime DATETIME
SET @DateTime = '2018-11-23 10:03:23'
SELECT CONVERT(VARCHAR(100),@DateTime,121 )
edited Nov 23 '18 at 4:50
K.Dᴀᴠɪs
7,189112439
answered Nov 23 '18 at 4:30
DilkhushDilkhush
112
DECLARE @DateTime DATETIME
SET @DateTime = '2018-11-23 10:03:23'
SELECT CONVERT(VARCHAR(100),@DateTime,121 )
edited Nov 23 '18 at 4:50
K.Dᴀᴠɪs
7,189112439
answered Nov 23 '18 at 4:30
DilkhushDilkhush
112
edited Nov 23 '18 at 4:50
K.Dᴀᴠɪs
7,189112439
edited Nov 23 '18 at 4:50
K.Dᴀᴠɪs
7,189112439
edited Nov 23 '18 at 4:50
K.Dᴀᴠɪs
7,189112439
7,189112439
answered Nov 23 '18 at 4:30
DilkhushDilkhush
112
answered Nov 23 '18 at 4:30
DilkhushDilkhush
112
answered Nov 23 '18 at 4:30
DilkhushDilkhush
112
112
add a comment |
add a comment |
-1
CONVERT(VARCHAR, GETDATE(), 23)
edited Apr 29 '14 at 16:47
Ashkan Mobayen Khiabani
20.3k1565115
answered Apr 29 '14 at 16:13
GabrielGabriel
372310
Doesn't work at all.
– Daria
Oct 8 '14 at 6:38
add a comment |
-1
CONVERT(VARCHAR, GETDATE(), 23)
edited Apr 29 '14 at 16:47
Ashkan Mobayen Khiabani
20.3k1565115
answered Apr 29 '14 at 16:13
GabrielGabriel
372310
Doesn't work at all.
– Daria
Oct 8 '14 at 6:38
add a comment |
-1
-1
-1
CONVERT(VARCHAR, GETDATE(), 23)
edited Apr 29 '14 at 16:47
Ashkan Mobayen Khiabani
20.3k1565115
answered Apr 29 '14 at 16:13
GabrielGabriel
372310
CONVERT(VARCHAR, GETDATE(), 23)
edited Apr 29 '14 at 16:47
Ashkan Mobayen Khiabani
20.3k1565115
answered Apr 29 '14 at 16:13
GabrielGabriel
372310
edited Apr 29 '14 at 16:47
Ashkan Mobayen Khiabani
20.3k1565115
edited Apr 29 '14 at 16:47
Ashkan Mobayen Khiabani
20.3k1565115
edited Apr 29 '14 at 16:47
Ashkan Mobayen Khiabani
20.3k1565115
20.3k1565115
answered Apr 29 '14 at 16:13
GabrielGabriel
372310
answered Apr 29 '14 at 16:13
GabrielGabriel
372310
answered Apr 29 '14 at 16:13
GabrielGabriel
372310
372310
Doesn't work at all.
– Daria
Oct 8 '14 at 6:38
add a comment |
Doesn't work at all.
– Daria
Oct 8 '14 at 6:38
Doesn't work at all.
– Daria
Oct 8 '14 at 6:38
Doesn't work at all.
– Daria
Oct 8 '14 at 6:38
add a comment |
-3
You don't say what language but I am assuming C#/.NET because it has a native DateTime data type. In that case just convert it using the ToString method and use a format specifier such as:
DateTime d = DateTime.Today;
string result = d.ToString("yyyy-MM-dd");
However, I would caution against using this in a database query or concatenated into a SQL statement. Databases require a specific formatting string to be used. You are better off zeroing out the time part and using the DateTime as a SQL parameter if that is what you are trying to accomplish.
edited Nov 25 '15 at 11:14
Sabyasachi Mishra
1,2472038
answered Sep 16 '08 at 16:52
Johnny BravadoJohnny Bravado
972
In the question, it's mentioned "I am working on a query in Sql Server 2005".
– InkHeart
Aug 23 '16 at 1:04
4
@InkHeart and all the down-voters - This answer was posted in 2008. If you check the history of edits for the original question there were no tags and/or any other technology-related information included.
– GSazheniuk
Apr 6 '17 at 21:33
add a comment |
-3
You don't say what language but I am assuming C#/.NET because it has a native DateTime data type. In that case just convert it using the ToString method and use a format specifier such as:
DateTime d = DateTime.Today;
string result = d.ToString("yyyy-MM-dd");
However, I would caution against using this in a database query or concatenated into a SQL statement. Databases require a specific formatting string to be used. You are better off zeroing out the time part and using the DateTime as a SQL parameter if that is what you are trying to accomplish.
edited Nov 25 '15 at 11:14
Sabyasachi Mishra
1,2472038
answered Sep 16 '08 at 16:52
Johnny BravadoJohnny Bravado
972
In the question, it's mentioned "I am working on a query in Sql Server 2005".
– InkHeart
Aug 23 '16 at 1:04
4
@InkHeart and all the down-voters - This answer was posted in 2008. If you check the history of edits for the original question there were no tags and/or any other technology-related information included.
– GSazheniuk
Apr 6 '17 at 21:33
add a comment |
-3
-3
-3
You don't say what language but I am assuming C#/.NET because it has a native DateTime data type. In that case just convert it using the ToString method and use a format specifier such as:
DateTime d = DateTime.Today;
string result = d.ToString("yyyy-MM-dd");
However, I would caution against using this in a database query or concatenated into a SQL statement. Databases require a specific formatting string to be used. You are better off zeroing out the time part and using the DateTime as a SQL parameter if that is what you are trying to accomplish.
edited Nov 25 '15 at 11:14
Sabyasachi Mishra
1,2472038
answered Sep 16 '08 at 16:52
Johnny BravadoJohnny Bravado
972
You don't say what language but I am assuming C#/.NET because it has a native DateTime data type. In that case just convert it using the ToString method and use a format specifier such as:
DateTime d = DateTime.Today;
string result = d.ToString("yyyy-MM-dd");
However, I would caution against using this in a database query or concatenated into a SQL statement. Databases require a specific formatting string to be used. You are better off zeroing out the time part and using the DateTime as a SQL parameter if that is what you are trying to accomplish.
edited Nov 25 '15 at 11:14
Sabyasachi Mishra
1,2472038
answered Sep 16 '08 at 16:52
Johnny BravadoJohnny Bravado
972
edited Nov 25 '15 at 11:14
Sabyasachi Mishra
1,2472038
edited Nov 25 '15 at 11:14
Sabyasachi Mishra
1,2472038
edited Nov 25 '15 at 11:14
Sabyasachi Mishra
1,2472038
1,2472038
answered Sep 16 '08 at 16:52
Johnny BravadoJohnny Bravado
972
answered Sep 16 '08 at 16:52
Johnny BravadoJohnny Bravado
972
answered Sep 16 '08 at 16:52
Johnny BravadoJohnny Bravado
972
972
In the question, it's mentioned "I am working on a query in Sql Server 2005".
– InkHeart
Aug 23 '16 at 1:04
4
@InkHeart and all the down-voters - This answer was posted in 2008. If you check the history of edits for the original question there were no tags and/or any other technology-related information included.
– GSazheniuk
Apr 6 '17 at 21:33
add a comment |
In the question, it's mentioned "I am working on a query in Sql Server 2005".
– InkHeart
Aug 23 '16 at 1:04
4
@InkHeart and all the down-voters - This answer was posted in 2008. If you check the history of edits for the original question there were no tags and/or any other technology-related information included.
– GSazheniuk
Apr 6 '17 at 21:33
In the question, it's mentioned "I am working on a query in Sql Server 2005".
– InkHeart
Aug 23 '16 at 1:04
In the question, it's mentioned "I am working on a query in Sql Server 2005".
– InkHeart
Aug 23 '16 at 1:04
4
4
@InkHeart and all the down-voters - This answer was posted in 2008. If you check the history of edits for the original question there were no tags and/or any other technology-related information included.
– GSazheniuk
Apr 6 '17 at 21:33
@InkHeart and all the down-voters - This answer was posted in 2008. If you check the history of edits for the original question there were no tags and/or any other technology-related information included.
– GSazheniuk
Apr 6 '17 at 21:33
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- 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.
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f74385%2fhow-to-convert-datetime-to-varchar%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
CONVERT, see MSDN documentation.– Jason Cohen
Sep 16 '08 at 16:46
be aware that YYYY-MM-DD is ambiguous, depending on your language settings. best to use ISO standard YYYYMMDD, see this blog post
– Andy Irving
Sep 16 '08 at 17:49