我已经搜索了很多主题的变体,但没有真正的答案。
我需要将datetime
的MAX
转换为date
,并将其插入到我创建的新表中。
这是我的代码:
INSERT INTO [dbo].[OptOutEmails]([emailaddressid],[FirstOptOutDate], [LastOptOutDate], [bu_id], [brand_id])
SELECT [dbo].[DistinctEmailIds].[emailaddressid] as email
,[dbo].[emailaddresses_summary].[bu_id]
,[dbo].[emailaddresses_summary].[brand_id]
, CONVERT(datetime, max([emailaddresses_summary].[FirstOptOutDate])) AS FirstOptOutDate
, CONVERT(datetime, max([emailaddresses_summary].[LastOptOutDate])) AS LastOptOutDate
FROM [dbo].[DistinctEmailIds]
JOIN [dbo].[emailaddresses_summary]
ON [dbo].[DistinctEmailIds].[emailaddressid] = [dbo].[emailaddresses_summary].[emailaddressid]
WHERE [dbo].[emailaddresses_summary].[LastOptOutDate] IS NOT NULL
GROUP BY [dbo].[DistinctEmailIds].[emailaddressid],[dbo].[emailaddresses_summary].[bu_id],[dbo].[emailaddresses_summary].[brand_id];
GO
它需要进入这个表格:
[emailaddressid] int not null PRIMARY KEY
, [FirstOptOutDate] datetime null
, [LastOptOutDate] datetime null
, [bu_id] int null
, [brand_id] int null
我收到的错误消息:消息257,级别16,状态3,第56行
不允许从数据类型datetime隐式转换为int。使用CONVERT函数运行此查询。
我想的都试过了。我的代码中生成的CONVERT
语句来自一个字符串,我认为它可以解决这个问题。什么都不起作用。请帮忙?
您得到错误是因为列顺序错误;insert语句中的列与select语句中的顺序不匹配:
INSERT INTO [dbo].[OptOutEmails]([emailaddressid],[FirstOptOutDate], [LastOptOutDate], [bu_id], [brand_id])
SELECT [dbo].[DistinctEmailIds].[emailaddressid] as email
,[dbo].[emailaddresses_summary].[bu_id]
,[dbo].[emailaddresses_summary].[brand_id]
, CONVERT(datetime, max([emailaddresses_summary].[FirstOptOutDate])) AS FirstOptOutDate
, CONVERT(datetime, max([emailaddresses_summary].[LastOptOutDate])) AS LastOptOutDate
你想要这个:
INSERT INTO [dbo].[OptOutEmails]([emailaddressid],[FirstOptOutDate], [LastOptOutDate], [bu_id], [brand_id])
SELECT [dbo].[DistinctEmailIds].[emailaddressid] as email
, CONVERT(datetime, max([emailaddresses_summary].[FirstOptOutDate])) AS FirstOptOutDate
, CONVERT(datetime, max([emailaddresses_summary].[LastOptOutDate])) AS LastOptOutDate
, [dbo].[emailaddresses_summary].[bu_id]
, [dbo].[emailaddresses_summary].[brand_id]
此外,您可能希望将聚合列更改为
, MAX(CONVERT(datetime, [emailaddresses_summary].[FirstOptOutDate])) AS FirstOptOutDate
, MAX(CONVERT(datetime, [emailaddresses_summary].[LastOptOutDate])) AS LastOptOutDate
因为这更好地传达了意图,并将MAX聚合函数应用于转换后的日期时间列,而不是以前的任何列(varchar可能是?)。
第四列bu_id的类型为int
Convert正在进行日期时间的转换,因此您会收到错误
, CONVERT(datetime, max([emailaddresses_summary])
您可以将其转换为int,它将返回自1900年1月1日以来的天数或者您需要将您的列定义为日期时间
brand_id列存在相同问题
, CONVERT(int, max([emailaddresses_summary])