How to set part of a datetime
up vote
2
down vote
favorite
I need to set just the hour of a datetime to a certain value. I know I can add it the difference to the desired value, or create a new datetime with parts from the original one, but isn't there a cleaner way? Something like:
declare @d datetime = '09/08/2012 09:14:55'
set @d = SETDATEPARTORSOMETHINGLIKETHAT (hour, @d, 23)
Thanks a lot.
sql
sql-server datetime asked Sep 12 '12 at 13:15
jesusiniesta
486928
|
show 3 more comments
up vote
2
down vote
favorite
I need to set just the hour of a datetime to a certain value. I know I can add it the difference to the desired value, or create a new datetime with parts from the original one, but isn't there a cleaner way? Something like:
declare @d datetime = '09/08/2012 09:14:55'
set @d = SETDATEPARTORSOMETHINGLIKETHAT (hour, @d, 23)
Thanks a lot.
sql
sql-server datetime asked Sep 12 '12 at 13:15
jesusiniesta
486928
Nope. It's not listed in the available functions. You could perhaps write your own function to do it, but internally to the function, it would still be using the same basic SQL (DateAdd) you'd use anyway.
– David
Sep 12 '12 at 13:19
The question was 'isn't there a cleaner way?', and his answer is 'no', so I guess strictly speaking it is the objective answer, even if not the most useful (but not useless at all, since I was convinced that there would be a function somewhere).
– jesusiniesta
Sep 12 '12 at 13:49
@AaronBertrand - It's answering the question he asked "Isn't there a cleaner way?" He already stated in his question: "I know I can add it the difference to the desired value, or create a new datetime with parts from the original one" which was what you answered. He asked, pecifically, is there a cleaner way. Creating a function to do the work is a cleaner way, but it is using the same method he mentioned under the hood. "No" is the answer to the question. You just told him what he already said he knew how to do in code.
– David
Sep 12 '12 at 13:59
Perhaps, then, this question should be moved to the Philosophy.SE beta since its format is so esoteric. As it stands, since it's on SO, we should strive for answers that solve actual problems.
– swasheck
Sep 12 '12 at 14:12
@AaronBertrand - I don't want to argue, but asking if there's a more efficient way (and if so, what is it) is perfectly valid. A huge portion of the time, the answer is yes. This was just one of the rare cases where the answer is no. His question was fine, and your answer was good. I really don't see why you're so bent out of shape. You're getting plenty of upvotes (including one from me) and your answer will be helpful to future users. Knowning what's not possible with a certain technology is an important thing to know. I'm sorry to have offended you.
– David
Sep 12 '12 at 14:20
|
show 3 more comments
up vote
2
down vote
favorite
up vote
2
down vote
favorite
I need to set just the hour of a datetime to a certain value. I know I can add it the difference to the desired value, or create a new datetime with parts from the original one, but isn't there a cleaner way? Something like:
declare @d datetime = '09/08/2012 09:14:55'
set @d = SETDATEPARTORSOMETHINGLIKETHAT (hour, @d, 23)
Thanks a lot.
sql
sql-server datetime asked Sep 12 '12 at 13:15
jesusiniesta
486928
I need to set just the hour of a datetime to a certain value. I know I can add it the difference to the desired value, or create a new datetime with parts from the original one, but isn't there a cleaner way? Something like:
declare @d datetime = '09/08/2012 09:14:55'
set @d = SETDATEPARTORSOMETHINGLIKETHAT (hour, @d, 23)
Thanks a lot.
sql
sql-server datetime sql
sql-server datetime asked Sep 12 '12 at 13:15
jesusiniesta
486928
asked Sep 12 '12 at 13:15
jesusiniesta
486928
asked Sep 12 '12 at 13:15
jesusiniesta
486928
asked Sep 12 '12 at 13:15
jesusiniesta
486928
asked Sep 12 '12 at 13:15
jesusiniesta
486928
486928
Nope. It's not listed in the available functions. You could perhaps write your own function to do it, but internally to the function, it would still be using the same basic SQL (DateAdd) you'd use anyway.
– David
Sep 12 '12 at 13:19
The question was 'isn't there a cleaner way?', and his answer is 'no', so I guess strictly speaking it is the objective answer, even if not the most useful (but not useless at all, since I was convinced that there would be a function somewhere).
– jesusiniesta
Sep 12 '12 at 13:49
@AaronBertrand - It's answering the question he asked "Isn't there a cleaner way?" He already stated in his question: "I know I can add it the difference to the desired value, or create a new datetime with parts from the original one" which was what you answered. He asked, pecifically, is there a cleaner way. Creating a function to do the work is a cleaner way, but it is using the same method he mentioned under the hood. "No" is the answer to the question. You just told him what he already said he knew how to do in code.
– David
Sep 12 '12 at 13:59
Perhaps, then, this question should be moved to the Philosophy.SE beta since its format is so esoteric. As it stands, since it's on SO, we should strive for answers that solve actual problems.
– swasheck
Sep 12 '12 at 14:12
@AaronBertrand - I don't want to argue, but asking if there's a more efficient way (and if so, what is it) is perfectly valid. A huge portion of the time, the answer is yes. This was just one of the rare cases where the answer is no. His question was fine, and your answer was good. I really don't see why you're so bent out of shape. You're getting plenty of upvotes (including one from me) and your answer will be helpful to future users. Knowning what's not possible with a certain technology is an important thing to know. I'm sorry to have offended you.
– David
Sep 12 '12 at 14:20
|
show 3 more comments
Nope. It's not listed in the available functions. You could perhaps write your own function to do it, but internally to the function, it would still be using the same basic SQL (DateAdd) you'd use anyway.
– David
Sep 12 '12 at 13:19
The question was 'isn't there a cleaner way?', and his answer is 'no', so I guess strictly speaking it is the objective answer, even if not the most useful (but not useless at all, since I was convinced that there would be a function somewhere).
– jesusiniesta
Sep 12 '12 at 13:49
@AaronBertrand - It's answering the question he asked "Isn't there a cleaner way?" He already stated in his question: "I know I can add it the difference to the desired value, or create a new datetime with parts from the original one" which was what you answered. He asked, pecifically, is there a cleaner way. Creating a function to do the work is a cleaner way, but it is using the same method he mentioned under the hood. "No" is the answer to the question. You just told him what he already said he knew how to do in code.
– David
Sep 12 '12 at 13:59
Perhaps, then, this question should be moved to the Philosophy.SE beta since its format is so esoteric. As it stands, since it's on SO, we should strive for answers that solve actual problems.
– swasheck
Sep 12 '12 at 14:12
@AaronBertrand - I don't want to argue, but asking if there's a more efficient way (and if so, what is it) is perfectly valid. A huge portion of the time, the answer is yes. This was just one of the rare cases where the answer is no. His question was fine, and your answer was good. I really don't see why you're so bent out of shape. You're getting plenty of upvotes (including one from me) and your answer will be helpful to future users. Knowning what's not possible with a certain technology is an important thing to know. I'm sorry to have offended you.
– David
Sep 12 '12 at 14:20
Nope. It's not listed in the available functions. You could perhaps write your own function to do it, but internally to the function, it would still be using the same basic SQL (DateAdd) you'd use anyway.
– David
Sep 12 '12 at 13:19
Nope. It's not listed in the available functions. You could perhaps write your own function to do it, but internally to the function, it would still be using the same basic SQL (DateAdd) you'd use anyway.
– David
Sep 12 '12 at 13:19
The question was 'isn't there a cleaner way?', and his answer is 'no', so I guess strictly speaking it is the objective answer, even if not the most useful (but not useless at all, since I was convinced that there would be a function somewhere).
– jesusiniesta
Sep 12 '12 at 13:49
The question was 'isn't there a cleaner way?', and his answer is 'no', so I guess strictly speaking it is the objective answer, even if not the most useful (but not useless at all, since I was convinced that there would be a function somewhere).
– jesusiniesta
Sep 12 '12 at 13:49
@AaronBertrand - It's answering the question he asked "Isn't there a cleaner way?" He already stated in his question: "I know I can add it the difference to the desired value, or create a new datetime with parts from the original one" which was what you answered. He asked, pecifically, is there a cleaner way. Creating a function to do the work is a cleaner way, but it is using the same method he mentioned under the hood. "No" is the answer to the question. You just told him what he already said he knew how to do in code.
– David
Sep 12 '12 at 13:59
@AaronBertrand - It's answering the question he asked "Isn't there a cleaner way?" He already stated in his question: "I know I can add it the difference to the desired value, or create a new datetime with parts from the original one" which was what you answered. He asked, pecifically, is there a cleaner way. Creating a function to do the work is a cleaner way, but it is using the same method he mentioned under the hood. "No" is the answer to the question. You just told him what he already said he knew how to do in code.
– David
Sep 12 '12 at 13:59
Perhaps, then, this question should be moved to the Philosophy.SE beta since its format is so esoteric. As it stands, since it's on SO, we should strive for answers that solve actual problems.
– swasheck
Sep 12 '12 at 14:12
Perhaps, then, this question should be moved to the Philosophy.SE beta since its format is so esoteric. As it stands, since it's on SO, we should strive for answers that solve actual problems.
– swasheck
Sep 12 '12 at 14:12
@AaronBertrand - I don't want to argue, but asking if there's a more efficient way (and if so, what is it) is perfectly valid. A huge portion of the time, the answer is yes. This was just one of the rare cases where the answer is no. His question was fine, and your answer was good. I really don't see why you're so bent out of shape. You're getting plenty of upvotes (including one from me) and your answer will be helpful to future users. Knowning what's not possible with a certain technology is an important thing to know. I'm sorry to have offended you.
– David
Sep 12 '12 at 14:20
@AaronBertrand - I don't want to argue, but asking if there's a more efficient way (and if so, what is it) is perfectly valid. A huge portion of the time, the answer is yes. This was just one of the rare cases where the answer is no. His question was fine, and your answer was good. I really don't see why you're so bent out of shape. You're getting plenty of upvotes (including one from me) and your answer will be helpful to future users. Knowning what's not possible with a certain technology is an important thing to know. I'm sorry to have offended you.
– David
Sep 12 '12 at 14:20
|
show 3 more comments
1 Answer
1
active
oldest
votes
up vote
6
down vote
accepted
DECLARE @TargetHour TINYINT = 23;
DECLARE @d datetime = '09/08/2012 09:14:55';
SET @d = DATEADD(HOUR, @TargetHour - DATEPART(HOUR, @d), @d);
SELECT @d;
Result:
2012-09-08 23:14:55.000
(And yes, this will work if the @TargetHour is less than the current hour, e.g. 5 or 7.)
I'd be very careful though about using ambiguous and error-prone formats like m/d/y. Case in point: I don't even know if you meant September 8th or August 9th, and SQL Server isn't going to know either - it needs to use regional settings etc. to figure it out, and if you give your code to someone with different settings, it will generate an error or, even worse, accept the wrong date silently. You should use:
DECLARE @d datetime = '2012-09-08T09:14:55';
- Bad habits to kick : mis-handling date / range queries
answered Sep 12 '12 at 13:17
Aaron Bertrand
206k27358401
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
6
down vote
accepted
DECLARE @TargetHour TINYINT = 23;
DECLARE @d datetime = '09/08/2012 09:14:55';
SET @d = DATEADD(HOUR, @TargetHour - DATEPART(HOUR, @d), @d);
SELECT @d;
Result:
2012-09-08 23:14:55.000
(And yes, this will work if the @TargetHour is less than the current hour, e.g. 5 or 7.)
I'd be very careful though about using ambiguous and error-prone formats like m/d/y. Case in point: I don't even know if you meant September 8th or August 9th, and SQL Server isn't going to know either - it needs to use regional settings etc. to figure it out, and if you give your code to someone with different settings, it will generate an error or, even worse, accept the wrong date silently. You should use:
DECLARE @d datetime = '2012-09-08T09:14:55';
- Bad habits to kick : mis-handling date / range queries
answered Sep 12 '12 at 13:17
Aaron Bertrand
206k27358401
add a comment |
up vote
6
down vote
accepted
DECLARE @TargetHour TINYINT = 23;
DECLARE @d datetime = '09/08/2012 09:14:55';
SET @d = DATEADD(HOUR, @TargetHour - DATEPART(HOUR, @d), @d);
SELECT @d;
Result:
2012-09-08 23:14:55.000
(And yes, this will work if the @TargetHour is less than the current hour, e.g. 5 or 7.)
I'd be very careful though about using ambiguous and error-prone formats like m/d/y. Case in point: I don't even know if you meant September 8th or August 9th, and SQL Server isn't going to know either - it needs to use regional settings etc. to figure it out, and if you give your code to someone with different settings, it will generate an error or, even worse, accept the wrong date silently. You should use:
DECLARE @d datetime = '2012-09-08T09:14:55';
- Bad habits to kick : mis-handling date / range queries
answered Sep 12 '12 at 13:17
Aaron Bertrand
206k27358401
add a comment |
up vote
6
down vote
accepted
up vote
6
down vote
accepted
DECLARE @TargetHour TINYINT = 23;
DECLARE @d datetime = '09/08/2012 09:14:55';
SET @d = DATEADD(HOUR, @TargetHour - DATEPART(HOUR, @d), @d);
SELECT @d;
Result:
2012-09-08 23:14:55.000
(And yes, this will work if the @TargetHour is less than the current hour, e.g. 5 or 7.)
I'd be very careful though about using ambiguous and error-prone formats like m/d/y. Case in point: I don't even know if you meant September 8th or August 9th, and SQL Server isn't going to know either - it needs to use regional settings etc. to figure it out, and if you give your code to someone with different settings, it will generate an error or, even worse, accept the wrong date silently. You should use:
DECLARE @d datetime = '2012-09-08T09:14:55';
- Bad habits to kick : mis-handling date / range queries
answered Sep 12 '12 at 13:17
Aaron Bertrand
206k27358401
DECLARE @TargetHour TINYINT = 23;
DECLARE @d datetime = '09/08/2012 09:14:55';
SET @d = DATEADD(HOUR, @TargetHour - DATEPART(HOUR, @d), @d);
SELECT @d;
Result:
2012-09-08 23:14:55.000
(And yes, this will work if the @TargetHour is less than the current hour, e.g. 5 or 7.)
I'd be very careful though about using ambiguous and error-prone formats like m/d/y. Case in point: I don't even know if you meant September 8th or August 9th, and SQL Server isn't going to know either - it needs to use regional settings etc. to figure it out, and if you give your code to someone with different settings, it will generate an error or, even worse, accept the wrong date silently. You should use:
DECLARE @d datetime = '2012-09-08T09:14:55';
- Bad habits to kick : mis-handling date / range queries
answered Sep 12 '12 at 13:17
Aaron Bertrand
206k27358401
edited Nov 10 at 22:39
answered Sep 12 '12 at 13:17
Aaron Bertrand
206k27358401
answered Sep 12 '12 at 13:17
Aaron Bertrand
206k27358401
answered Sep 12 '12 at 13:17
Aaron Bertrand
206k27358401
206k27358401
add a comment |
add a comment |
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%2f12389084%2fhow-to-set-part-of-a-datetime%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
Nope. It's not listed in the available functions. You could perhaps write your own function to do it, but internally to the function, it would still be using the same basic SQL (DateAdd) you'd use anyway.
– David
Sep 12 '12 at 13:19
The question was 'isn't there a cleaner way?', and his answer is 'no', so I guess strictly speaking it is the objective answer, even if not the most useful (but not useless at all, since I was convinced that there would be a function somewhere).
– jesusiniesta
Sep 12 '12 at 13:49
@AaronBertrand - It's answering the question he asked "Isn't there a cleaner way?" He already stated in his question: "I know I can add it the difference to the desired value, or create a new datetime with parts from the original one" which was what you answered. He asked, pecifically, is there a cleaner way. Creating a function to do the work is a cleaner way, but it is using the same method he mentioned under the hood. "No" is the answer to the question. You just told him what he already said he knew how to do in code.
– David
Sep 12 '12 at 13:59
Perhaps, then, this question should be moved to the Philosophy.SE beta since its format is so esoteric. As it stands, since it's on SO, we should strive for answers that solve actual problems.
– swasheck
Sep 12 '12 at 14:12
@AaronBertrand - I don't want to argue, but asking if there's a more efficient way (and if so, what is it) is perfectly valid. A huge portion of the time, the answer is yes. This was just one of the rare cases where the answer is no. His question was fine, and your answer was good. I really don't see why you're so bent out of shape. You're getting plenty of upvotes (including one from me) and your answer will be helpful to future users. Knowning what's not possible with a certain technology is an important thing to know. I'm sorry to have offended you.
– David
Sep 12 '12 at 14:20