March 16, 2017 at 5:27 pm
SELECT CONVERT(VARCHAR(10), GETDATE()+2, 1) AS 'Date'
union
SELECT DISTINCT User.id FROM User
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value '03/18/17' to data type int.
Without the union, it is fine, but with the union, it fails.
What have I missed?
Thank you
March 16, 2017 at 5:35 pm
What type is User.ID? Sounds like it's an integer, and GETDATE does not return an integer, but a datetime.
March 16, 2017 at 5:58 pm
pietlinden - Thursday, March 16, 2017 5:35 PMWhat type is User.ID? Sounds like it's an integer, and GETDATE does not return an integer, but a datetime.
Expanding a little on this: both items returned need to have the same data type.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 17, 2017 at 11:21 am
Correct, this is an integer, but I cannot seem to convert the datetime to an integer with a format of mm/dd/yy .
March 17, 2017 at 11:30 am
elzool - Friday, March 17, 2017 11:21 AMCorrect, this is an integer, but I cannot seem to convert the datetime to an integer with a format of mm/dd/yy .
How can mm/dd/yy ever be considered an integer?
This is roughly what you are asking for, but with no slashes and no leading zero.
MONTH(GETDATE()) * 10000 + DAY(GETDATE()) * 100 + YEAR(GETDATE()) - 2000
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 17, 2017 at 3:20 pm
Rather than converting the date to INT, I would convert the User.id to Varchar. UNION returns the data type that has precedence among the various results. Currently your date is Varchar, and the user.id is INT. Since INT has precedence over Varchar, the UNION wants to convert all the results to INT. If you convert the id to Varchar, all your results will be varchar and it should stop trying to convert your date.
March 17, 2017 at 3:24 pm
elzool - Thursday, March 16, 2017 5:27 PM
SELECT CONVERT(VARCHAR(10), GETDATE()+2, 1) AS 'Date'
union
SELECT DISTINCT User.id FROM UserMsg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value '03/18/17' to data type int.Without the union, it is fine, but with the union, it fails.
What have I missed?Thank you
Return two columns, one for the INT and one for the date. Worry about the collision later.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 17, 2017 at 4:36 pm
Possibly an easier answer - convert User.id to a varchar.
edit - guess I shouldn't have left this open for a bit before answering... others beat me to that LOL.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply