November 9, 2016 at 10:47 am
Hello,
I have a stored proc I am trying to run but I am getting the error
"Conversion failed when converting date and/or time from character string"
The original table looks like this
Name nvarchar(100)
Timestamp datetime
Number decimal(6,2)
The table I am trying to insert into looks like this
Name nvarchar(100)
Timestamp date
Number deciaml(6,2)
Here is my code
INSERT INTO dbo.NewDB (Application, Timestamp, Number)
SELECT
CAST(Timestamp AS DATE) as Date,
Application,
AVG(CAST(Number AS decimal(6,2))) as Average_Number
FROM dbo.OriginalDB
WHERE Application = 'APPNAME'
GROUP BY CAST(Timestamp AS DATE), Application
Can anyone see what I am doing wrong? If I run the select by itself, I get the data I want.
EDIT: One more note
Dates look like this in OriginalDB
2016-11-07 07:21:00.000
I am trying to combine times so the NewDB dates look like this
2016-11-07
November 9, 2016 at 11:02 am
Matt.Altman (11/9/2016)
Hello,I have a stored proc I am trying to run but I am getting the error
"Conversion failed when converting date and/or time from character string"
The original table looks like this
Name nvarchar(100)
Timestamp datetime
Number decimal(6,2)
The table I am trying to insert into looks like this
Name nvarchar(100)
Timestamp date
Number deciaml(6,2)
Here is my code
INSERT INTO dbo.NewDB (Application, Timestamp, Number)
SELECT
CAST(Timestamp AS DATE) as Date,
Application,
AVG(CAST(Number AS decimal(6,2))) as Average_Number
FROM dbo.OriginalDB
WHERE Application = 'APPNAME'
GROUP BY CAST(Timestamp AS DATE), Application
Can anyone see what I am doing wrong? If I run the select by itself, I get the data I want.
EDIT: One more note
Dates look like this in OriginalDB
2016-11-07 07:21:00.000
I am trying to combine times so the NewDB dates look like this
2016-11-07
Your SELECT list appears to be in a different order from the INSERT list.
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
November 9, 2016 at 11:05 am
haha wow. Thank you for catching that.
November 9, 2016 at 11:08 am
Matt.Altman (11/9/2016)
haha wow. Thank you for catching that.
NP. Sometimes a fresh pair of eyes is all that's needed.
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
November 9, 2016 at 11:44 am
Phil Parkin (11/9/2016)
Matt.Altman (11/9/2016)
haha wow. Thank you for catching that.NP. Sometimes a fresh pair of eyes is all that's needed.
This is were sys.dm_exec_describe_first_result_set comes in handy, just generate the insert statement from the output of the view and you will never get it wrong 😉
😎
November 9, 2016 at 11:52 am
Thank you. I will put that on my things to do before stressing myself out.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply