Converting date and/or time from character string Conversion failed

Multi tool use
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
declare @orderwhere varchar(5000)
declare @Pdate DateTime
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate=' + @Pdate
exec('select Product.ProductID
from Product
where ' + @orderwhere)
I get these errors:
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.

add a comment |
declare @orderwhere varchar(5000)
declare @Pdate DateTime
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate=' + @Pdate
exec('select Product.ProductID
from Product
where ' + @orderwhere)
I get these errors:
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.

2
Don't edit the question in a way that makes already given answers look stupid. If you have solved one problem and have another instead ask a new question and probably delete the old one.
– sticky bit
Jan 3 at 19:23
Agreed with @stickybit here. The edit is completely different from the original question and can cause unnecessary downvotes.
– scsimon
Jan 3 at 19:29
add a comment |
declare @orderwhere varchar(5000)
declare @Pdate DateTime
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate=' + @Pdate
exec('select Product.ProductID
from Product
where ' + @orderwhere)
I get these errors:
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.

declare @orderwhere varchar(5000)
declare @Pdate DateTime
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate=' + @Pdate
exec('select Product.ProductID
from Product
where ' + @orderwhere)
I get these errors:
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.


edited Jan 3 at 21:41
marc_s
584k13011241270
584k13011241270
asked Jan 3 at 18:52
Ritesh PatelRitesh Patel
95
95
2
Don't edit the question in a way that makes already given answers look stupid. If you have solved one problem and have another instead ask a new question and probably delete the old one.
– sticky bit
Jan 3 at 19:23
Agreed with @stickybit here. The edit is completely different from the original question and can cause unnecessary downvotes.
– scsimon
Jan 3 at 19:29
add a comment |
2
Don't edit the question in a way that makes already given answers look stupid. If you have solved one problem and have another instead ask a new question and probably delete the old one.
– sticky bit
Jan 3 at 19:23
Agreed with @stickybit here. The edit is completely different from the original question and can cause unnecessary downvotes.
– scsimon
Jan 3 at 19:29
2
2
Don't edit the question in a way that makes already given answers look stupid. If you have solved one problem and have another instead ask a new question and probably delete the old one.
– sticky bit
Jan 3 at 19:23
Don't edit the question in a way that makes already given answers look stupid. If you have solved one problem and have another instead ask a new question and probably delete the old one.
– sticky bit
Jan 3 at 19:23
Agreed with @stickybit here. The edit is completely different from the original question and can cause unnecessary downvotes.
– scsimon
Jan 3 at 19:29
Agreed with @stickybit here. The edit is completely different from the original question and can cause unnecessary downvotes.
– scsimon
Jan 3 at 19:29
add a comment |
3 Answers
3
active
oldest
votes
Edit:
Note: This answer refers to the original question which had a line DECLARE @Pdate DATETIME = '1001-01-01'
and the error message "Conversion failed when converting date and/or time from character string." in it.
From the manual for datetime
:
Date range[:] January 1, 1753, through December 31, 9999
So your year is definitely to early for a datetime
.
You can try to use either a datetime2
, which ranges from 0001-01-01 through 9999-12-31 or just a date
with the same range as datetime2
(regarding the date part, not the time part of course).
They edited the question with a different error, thought his answers the original posted error.
– scsimon
Jan 3 at 19:21
add a comment |
You have to convert your @pdate to varchar
declare @orderwhere varchar(5000)
declare @Pdate DateTime
declare @Pdatec varchar(50)
set @Pdatec = CAST(@Pdate as varchar(50))
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ @Pdatec + ''''
exec('select Product.ProductID
from Product
where ' + @orderwhere)
and add quotation marks.
Try to test your dynamic queries by printing your results.
1
scsimon, the fix should work. The answer is creating a NEW variable (@Pdatec) and converting the date to a varchar.
– Mathew Paxinos
Jan 3 at 21:56
add a comment |
You need to convert the @Pdate
to varchar for the string concatenation.
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ cast(@Pdate as varchar) + ''''
Otherwise, it's treated as an addition operation. You'll also need to add in the extra apostrophe's, otherwise your execution fail. This is why it's always good to PRINT
the statement for debugging before you execute.
Example:
declare @orderwhere varchar(5000)
declare @Pdate date = '20160101'
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + cast(@Pdate as varchar) + ''''
print('select Product.ProductID
from Product
where ' + @orderwhere)
Or, using a datetime
declare @orderwhere varchar(5000)
declare @Pdate datetime = '20160101'
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + convert(varchar,@Pdate,120) + ''''
print('select Product.ProductID
from Product
where ' + @orderwhere)
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
});
}
});
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%2f54028171%2fconverting-date-and-or-time-from-character-string-conversion-failed%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Edit:
Note: This answer refers to the original question which had a line DECLARE @Pdate DATETIME = '1001-01-01'
and the error message "Conversion failed when converting date and/or time from character string." in it.
From the manual for datetime
:
Date range[:] January 1, 1753, through December 31, 9999
So your year is definitely to early for a datetime
.
You can try to use either a datetime2
, which ranges from 0001-01-01 through 9999-12-31 or just a date
with the same range as datetime2
(regarding the date part, not the time part of course).
They edited the question with a different error, thought his answers the original posted error.
– scsimon
Jan 3 at 19:21
add a comment |
Edit:
Note: This answer refers to the original question which had a line DECLARE @Pdate DATETIME = '1001-01-01'
and the error message "Conversion failed when converting date and/or time from character string." in it.
From the manual for datetime
:
Date range[:] January 1, 1753, through December 31, 9999
So your year is definitely to early for a datetime
.
You can try to use either a datetime2
, which ranges from 0001-01-01 through 9999-12-31 or just a date
with the same range as datetime2
(regarding the date part, not the time part of course).
They edited the question with a different error, thought his answers the original posted error.
– scsimon
Jan 3 at 19:21
add a comment |
Edit:
Note: This answer refers to the original question which had a line DECLARE @Pdate DATETIME = '1001-01-01'
and the error message "Conversion failed when converting date and/or time from character string." in it.
From the manual for datetime
:
Date range[:] January 1, 1753, through December 31, 9999
So your year is definitely to early for a datetime
.
You can try to use either a datetime2
, which ranges from 0001-01-01 through 9999-12-31 or just a date
with the same range as datetime2
(regarding the date part, not the time part of course).
Edit:
Note: This answer refers to the original question which had a line DECLARE @Pdate DATETIME = '1001-01-01'
and the error message "Conversion failed when converting date and/or time from character string." in it.
From the manual for datetime
:
Date range[:] January 1, 1753, through December 31, 9999
So your year is definitely to early for a datetime
.
You can try to use either a datetime2
, which ranges from 0001-01-01 through 9999-12-31 or just a date
with the same range as datetime2
(regarding the date part, not the time part of course).
edited Jan 3 at 19:25
answered Jan 3 at 19:15


sticky bitsticky bit
16k111733
16k111733
They edited the question with a different error, thought his answers the original posted error.
– scsimon
Jan 3 at 19:21
add a comment |
They edited the question with a different error, thought his answers the original posted error.
– scsimon
Jan 3 at 19:21
They edited the question with a different error, thought his answers the original posted error.
– scsimon
Jan 3 at 19:21
They edited the question with a different error, thought his answers the original posted error.
– scsimon
Jan 3 at 19:21
add a comment |
You have to convert your @pdate to varchar
declare @orderwhere varchar(5000)
declare @Pdate DateTime
declare @Pdatec varchar(50)
set @Pdatec = CAST(@Pdate as varchar(50))
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ @Pdatec + ''''
exec('select Product.ProductID
from Product
where ' + @orderwhere)
and add quotation marks.
Try to test your dynamic queries by printing your results.
1
scsimon, the fix should work. The answer is creating a NEW variable (@Pdatec) and converting the date to a varchar.
– Mathew Paxinos
Jan 3 at 21:56
add a comment |
You have to convert your @pdate to varchar
declare @orderwhere varchar(5000)
declare @Pdate DateTime
declare @Pdatec varchar(50)
set @Pdatec = CAST(@Pdate as varchar(50))
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ @Pdatec + ''''
exec('select Product.ProductID
from Product
where ' + @orderwhere)
and add quotation marks.
Try to test your dynamic queries by printing your results.
1
scsimon, the fix should work. The answer is creating a NEW variable (@Pdatec) and converting the date to a varchar.
– Mathew Paxinos
Jan 3 at 21:56
add a comment |
You have to convert your @pdate to varchar
declare @orderwhere varchar(5000)
declare @Pdate DateTime
declare @Pdatec varchar(50)
set @Pdatec = CAST(@Pdate as varchar(50))
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ @Pdatec + ''''
exec('select Product.ProductID
from Product
where ' + @orderwhere)
and add quotation marks.
Try to test your dynamic queries by printing your results.
You have to convert your @pdate to varchar
declare @orderwhere varchar(5000)
declare @Pdate DateTime
declare @Pdatec varchar(50)
set @Pdatec = CAST(@Pdate as varchar(50))
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ @Pdatec + ''''
exec('select Product.ProductID
from Product
where ' + @orderwhere)
and add quotation marks.
Try to test your dynamic queries by printing your results.
answered Jan 3 at 19:22


Alvaro ParraAlvaro Parra
6091722
6091722
1
scsimon, the fix should work. The answer is creating a NEW variable (@Pdatec) and converting the date to a varchar.
– Mathew Paxinos
Jan 3 at 21:56
add a comment |
1
scsimon, the fix should work. The answer is creating a NEW variable (@Pdatec) and converting the date to a varchar.
– Mathew Paxinos
Jan 3 at 21:56
1
1
scsimon, the fix should work. The answer is creating a NEW variable (@Pdatec) and converting the date to a varchar.
– Mathew Paxinos
Jan 3 at 21:56
scsimon, the fix should work. The answer is creating a NEW variable (@Pdatec) and converting the date to a varchar.
– Mathew Paxinos
Jan 3 at 21:56
add a comment |
You need to convert the @Pdate
to varchar for the string concatenation.
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ cast(@Pdate as varchar) + ''''
Otherwise, it's treated as an addition operation. You'll also need to add in the extra apostrophe's, otherwise your execution fail. This is why it's always good to PRINT
the statement for debugging before you execute.
Example:
declare @orderwhere varchar(5000)
declare @Pdate date = '20160101'
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + cast(@Pdate as varchar) + ''''
print('select Product.ProductID
from Product
where ' + @orderwhere)
Or, using a datetime
declare @orderwhere varchar(5000)
declare @Pdate datetime = '20160101'
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + convert(varchar,@Pdate,120) + ''''
print('select Product.ProductID
from Product
where ' + @orderwhere)
add a comment |
You need to convert the @Pdate
to varchar for the string concatenation.
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ cast(@Pdate as varchar) + ''''
Otherwise, it's treated as an addition operation. You'll also need to add in the extra apostrophe's, otherwise your execution fail. This is why it's always good to PRINT
the statement for debugging before you execute.
Example:
declare @orderwhere varchar(5000)
declare @Pdate date = '20160101'
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + cast(@Pdate as varchar) + ''''
print('select Product.ProductID
from Product
where ' + @orderwhere)
Or, using a datetime
declare @orderwhere varchar(5000)
declare @Pdate datetime = '20160101'
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + convert(varchar,@Pdate,120) + ''''
print('select Product.ProductID
from Product
where ' + @orderwhere)
add a comment |
You need to convert the @Pdate
to varchar for the string concatenation.
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ cast(@Pdate as varchar) + ''''
Otherwise, it's treated as an addition operation. You'll also need to add in the extra apostrophe's, otherwise your execution fail. This is why it's always good to PRINT
the statement for debugging before you execute.
Example:
declare @orderwhere varchar(5000)
declare @Pdate date = '20160101'
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + cast(@Pdate as varchar) + ''''
print('select Product.ProductID
from Product
where ' + @orderwhere)
Or, using a datetime
declare @orderwhere varchar(5000)
declare @Pdate datetime = '20160101'
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + convert(varchar,@Pdate,120) + ''''
print('select Product.ProductID
from Product
where ' + @orderwhere)
You need to convert the @Pdate
to varchar for the string concatenation.
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= '''+ cast(@Pdate as varchar) + ''''
Otherwise, it's treated as an addition operation. You'll also need to add in the extra apostrophe's, otherwise your execution fail. This is why it's always good to PRINT
the statement for debugging before you execute.
Example:
declare @orderwhere varchar(5000)
declare @Pdate date = '20160101'
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + cast(@Pdate as varchar) + ''''
print('select Product.ProductID
from Product
where ' + @orderwhere)
Or, using a datetime
declare @orderwhere varchar(5000)
declare @Pdate datetime = '20160101'
set @orderwhere = 'Product.ProductID = 1 And Product.ProductDate= ''' + convert(varchar,@Pdate,120) + ''''
print('select Product.ProductID
from Product
where ' + @orderwhere)
edited Jan 3 at 19:22
answered Jan 3 at 19:15


scsimonscsimon
22.3k51737
22.3k51737
add a comment |
add a comment |
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.
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%2f54028171%2fconverting-date-and-or-time-from-character-string-conversion-failed%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
EjNhewlKKFFzTV7
2
Don't edit the question in a way that makes already given answers look stupid. If you have solved one problem and have another instead ask a new question and probably delete the old one.
– sticky bit
Jan 3 at 19:23
Agreed with @stickybit here. The edit is completely different from the original question and can cause unnecessary downvotes.
– scsimon
Jan 3 at 19:29