将转换后的varchar插入datetime sql [英] Insert converted varchar into datetime sql

问题描述

我需要在表中插入 varchar .表中的类型是 datetime ，因此我需要对其进行转换.我不认为这会是一个大问题，但是它会一直插入 1900-01-01 00:00:00.000 而不是我想要的日期.当我对转换后的日期进行选择时，它确实会向我显示正确的日期.

I need to insert a varchar in my table. The type in the table is a datetime so I need to convert it. I didn't think this would be to big of a problem however it keeps inserting 1900-01-01 00:00:00.000 instead of the date I want. When I do a select with my converted date it does show me the correct date.

我将向您显示代码:

INSERT INTO Item (CategoryId, [Date], Content, CreatedOn)
   SELECT 
       CategoryId, Convert(datetime, '28/11/2012', 103), Content, GetDate()
   FROM 
       Item i
   JOIN 
       Category c ON i.CategoryId = c.Id
   JOIN 
       Division d ON d.Id = c.DivisionId
   WHERE 
       Date = Convert(datetime, '31/03/2005', 103) 
       AND d.Id = '142aaddf-5b63-4d53-a331-8eba9b0556c4'

where子句可以完美工作，并为我提供所需的过滤项目，除转换日期外，所有数据均已正确插入.就像我说的1900 -...

The where clause works perfectly and gives me the filtered items I need, all data is correctly inserted except for the converted date. The gives like I said 1900-...

如果我只是这样选择:

SELECT CategoryId, Convert(datetime, '28/11/2012', 103), Content, GetDate()
FROM Item i
JOIN Category c ON i.CategoryId = c.Id
JOIN Division d ON d.Id = c.DivisionId
WHERE Date = Convert(datetime, '31/03/2005', 103) AND d.Id = '142aaddf-5b63-4d53-a331-8eba9b0556c4'

我得到的正确日期是: 2012-11-28 00:00:00.000 .我尝试使用其他转换方式，例如:

I get the correct date being: 2012-11-28 00:00:00.000. I have tried to use a different conversion like:

Convert(datetime, '20121128')

但是那只会带来同样的问题.有人看到我在做什么错吗?

But that just gives the same problem. Anyone that sees what I'm doing wrong?

Thx

推荐答案

如果必须使用基于字符串的日期格式，则应选择一种安全且适用于每个SQL Server实例的日期格式，不管日期格式，语言和区域设置如何.

If you must use a string-based date format, you should pick one that is safe and works in every SQL Server instance, regardless of date format, language and regional settings.

该格式称为 ISO-8601 格式，也可以是

That format is known as ISO-8601 format and it's either

YYYYMMDD      (note: **NO** dashes!)

或

YYYY-MM-DDTHH:MM:SSS

用于 DATETIME 列.

所以不是

Convert(datetime, '28/11/2012', 103)

您应该使用

CAST('20121128' AS DATETIME)

然后您应该没问题.

如果您使用的是SQL Server 2008 -您还可以考虑使用 DATE (而不是 DATETIME )来解决仅需要日期(无时间部分).这将比使用 DATETIME 并使时间部分始终为 00:00:00

If you're on SQL Server 2008 - you could also look into using DATE (instead of DATETIME) for cases when you only need the date (no time portion). That would be even easier than using DATETIME and having the time portion always be 00:00:00

这篇关于将转换后的varchar插入datetime sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！