How to Remove Trailing Zeroes from a datetime column in SQL Server [duplicate]
up vote
-1
down vote
favorite
This question already has an answer here:
How to cast the DateTime to Time
3 answers
I have a column in a SQL Server database of datatype DATETIME.
Currently the value is in this format: 2054-12-31T00:00:00.0000000
I want to convert this column values into this format : 2054-12-31T00:00:00
This conversion of value should happen while I select the column in SELECT query statement at run time
sql-server tsql edited Nov 11 at 9:03
marc_s
567k12810981249
asked Nov 11 at 9:01
Srujan K.N.
166
marked as duplicate by Zohar Peled sql-server
Users with the sql-server badge can single-handedly close 
sql-server questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 11 at 9:53
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
up vote
-1
down vote
favorite
This question already has an answer here:
How to cast the DateTime to Time
3 answers
I have a column in a SQL Server database of datatype DATETIME.
Currently the value is in this format: 2054-12-31T00:00:00.0000000
I want to convert this column values into this format : 2054-12-31T00:00:00
This conversion of value should happen while I select the column in SELECT query statement at run time
sql-server tsql edited Nov 11 at 9:03
marc_s
567k12810981249
asked Nov 11 at 9:01
Srujan K.N.
166
marked as duplicate by Zohar Peled sql-server
Users with the sql-server badge can single-handedly close sql-server questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 11 at 9:53
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
2
"Currently the value is in the current format:" - No, It's not. You said it's a datetime column. if you just want a date , cast to date
– Mitch Wheat
Nov 11 at 9:03
add a comment |
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
This question already has an answer here:
How to cast the DateTime to Time
3 answers
I have a column in a SQL Server database of datatype DATETIME.
Currently the value is in this format: 2054-12-31T00:00:00.0000000
I want to convert this column values into this format : 2054-12-31T00:00:00
This conversion of value should happen while I select the column in SELECT query statement at run time
sql-server tsql edited Nov 11 at 9:03
marc_s
567k12810981249
asked Nov 11 at 9:01
Srujan K.N.
166
This question already has an answer here:
How to cast the DateTime to Time
3 answers
I have a column in a SQL Server database of datatype DATETIME.
Currently the value is in this format: 2054-12-31T00:00:00.0000000
I want to convert this column values into this format : 2054-12-31T00:00:00
This conversion of value should happen while I select the column in SELECT query statement at run time
This question already has an answer here:
How to cast the DateTime to Time
3 answers
sql-server tsql sql-server tsql edited Nov 11 at 9:03
marc_s
567k12810981249
asked Nov 11 at 9:01
Srujan K.N.
166
edited Nov 11 at 9:03
marc_s
567k12810981249
asked Nov 11 at 9:01
Srujan K.N.
166
edited Nov 11 at 9:03
marc_s
567k12810981249
edited Nov 11 at 9:03
marc_s
567k12810981249
edited Nov 11 at 9:03
marc_s
567k12810981249
567k12810981249
asked Nov 11 at 9:01
Srujan K.N.
166
asked Nov 11 at 9:01
Srujan K.N.
166
asked Nov 11 at 9:01
Srujan K.N.
166
166
marked as duplicate by Zohar Peled sql-server
Users with the sql-server badge can single-handedly close sql-server questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 11 at 9:53
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Zohar Peled sql-server
Users with the sql-server badge can single-handedly close sql-server questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 11 at 9:53
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
2
"Currently the value is in the current format:" - No, It's not. You said it's a datetime column. if you just want a date , cast to date
– Mitch Wheat
Nov 11 at 9:03
add a comment |
2
"Currently the value is in the current format:" - No, It's not. You said it's a datetime column. if you just want a date , cast to date
– Mitch Wheat
Nov 11 at 9:03
2
2
"Currently the value is in the current format:" - No, It's not. You said it's a datetime column. if you just want a date , cast to date
– Mitch Wheat
Nov 11 at 9:03
"Currently the value is in the current format:" - No, It's not. You said it's a datetime column. if you just want a date , cast to date
– Mitch Wheat
Nov 11 at 9:03
add a comment |
1 Answer
1
active
oldest
votes
up vote
4
down vote
DATETIME as stored in SQL Server doesn't have any "format" associated with it - it's stored as a binary, 8 byte value.
In order to convert that binary value into a human-readable format, you need to check out the different styles for CONVERTing a DATETIME column into a string representation: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
You're probably looking for style #126 - so you can use this in your SELECT query:
SELECT
CONVERT(VARCHAR(50), YourDateTimeColumn, 126)
and that should do it
answered Nov 11 at 9:05
marc_s
567k12810981249
1
@SrujanK.N. If an answer solved your problem you should accept it so that other people will know that the problem is solved.
– Zohar Peled
Nov 11 at 9:53
Zohar Peled I am facing issue while clicking on the accept option.That is why i had to comment below stating the issue is resolved
– Srujan K.N.
Nov 11 at 12:08
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
4
down vote
DATETIME as stored in SQL Server doesn't have any "format" associated with it - it's stored as a binary, 8 byte value.
In order to convert that binary value into a human-readable format, you need to check out the different styles for CONVERTing a DATETIME column into a string representation: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
You're probably looking for style #126 - so you can use this in your SELECT query:
SELECT
CONVERT(VARCHAR(50), YourDateTimeColumn, 126)
and that should do it
answered Nov 11 at 9:05
marc_s
567k12810981249
1
@SrujanK.N. If an answer solved your problem you should accept it so that other people will know that the problem is solved.
– Zohar Peled
Nov 11 at 9:53
Zohar Peled I am facing issue while clicking on the accept option.That is why i had to comment below stating the issue is resolved
– Srujan K.N.
Nov 11 at 12:08
add a comment |
up vote
4
down vote
DATETIME as stored in SQL Server doesn't have any "format" associated with it - it's stored as a binary, 8 byte value.
In order to convert that binary value into a human-readable format, you need to check out the different styles for CONVERTing a DATETIME column into a string representation: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
You're probably looking for style #126 - so you can use this in your SELECT query:
SELECT
CONVERT(VARCHAR(50), YourDateTimeColumn, 126)
and that should do it
answered Nov 11 at 9:05
marc_s
567k12810981249
1
@SrujanK.N. If an answer solved your problem you should accept it so that other people will know that the problem is solved.
– Zohar Peled
Nov 11 at 9:53
Zohar Peled I am facing issue while clicking on the accept option.That is why i had to comment below stating the issue is resolved
– Srujan K.N.
Nov 11 at 12:08
add a comment |
up vote
4
down vote
up vote
4
down vote
DATETIME as stored in SQL Server doesn't have any "format" associated with it - it's stored as a binary, 8 byte value.
In order to convert that binary value into a human-readable format, you need to check out the different styles for CONVERTing a DATETIME column into a string representation: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
You're probably looking for style #126 - so you can use this in your SELECT query:
SELECT
CONVERT(VARCHAR(50), YourDateTimeColumn, 126)
and that should do it
answered Nov 11 at 9:05
marc_s
567k12810981249
DATETIME as stored in SQL Server doesn't have any "format" associated with it - it's stored as a binary, 8 byte value.
In order to convert that binary value into a human-readable format, you need to check out the different styles for CONVERTing a DATETIME column into a string representation: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
You're probably looking for style #126 - so you can use this in your SELECT query:
SELECT
CONVERT(VARCHAR(50), YourDateTimeColumn, 126)
and that should do it
answered Nov 11 at 9:05
marc_s
567k12810981249
answered Nov 11 at 9:05
marc_s
567k12810981249
answered Nov 11 at 9:05
marc_s
567k12810981249
answered Nov 11 at 9:05
marc_s
567k12810981249
567k12810981249
1
@SrujanK.N. If an answer solved your problem you should accept it so that other people will know that the problem is solved.
– Zohar Peled
Nov 11 at 9:53
Zohar Peled I am facing issue while clicking on the accept option.That is why i had to comment below stating the issue is resolved
– Srujan K.N.
Nov 11 at 12:08
add a comment |
1
@SrujanK.N. If an answer solved your problem you should accept it so that other people will know that the problem is solved.
– Zohar Peled
Nov 11 at 9:53
Zohar Peled I am facing issue while clicking on the accept option.That is why i had to comment below stating the issue is resolved
– Srujan K.N.
Nov 11 at 12:08
1
1
@SrujanK.N. If an answer solved your problem you should accept it so that other people will know that the problem is solved.
– Zohar Peled
Nov 11 at 9:53
@SrujanK.N. If an answer solved your problem you should accept it so that other people will know that the problem is solved.
– Zohar Peled
Nov 11 at 9:53
Zohar Peled I am facing issue while clicking on the accept option.That is why i had to comment below stating the issue is resolved
– Srujan K.N.
Nov 11 at 12:08
Zohar Peled I am facing issue while clicking on the accept option.That is why i had to comment below stating the issue is resolved
– Srujan K.N.
Nov 11 at 12:08
add a comment |
2
"Currently the value is in the current format:" - No, It's not. You said it's a datetime column. if you just want a date , cast to date
– Mitch Wheat
Nov 11 at 9:03