I am dealing with a date/time from Australia (I am located in the USA). I am unable to get the following string to insert into a DATETIME2 column:
我正在处理来自澳大利亚的日期/时间(我位于美国)。我无法将以下字符串插入DATETIME2列:
2010/19/10 04:38:12.892
As you can see, it's formatted in yyyy/dd/mm HH:MM:ss.MMM format. I understand that the normal format is yyyy-mm-dd HH:MM:ss.MMM. What I am wondering is if there is a locality setting on SQL Server that I can change to get it to accept this format, or if I need to parse it and rearrange it myself.
如您所见,它的格式为yyyy / dd / mm HH:MM:ss.MMM格式。我知道正常的格式是yyyy-mm-dd HH:MM:ss.MMM。我想知道的是,如果在SQL Server上有一个位置设置,我可以更改它以使其接受此格式,或者如果我需要解析它并自己重新排列它。
EDIT: Just for your information, I have been importing a mm/dd/YYYY HH:MM:ss.MMM format string into the field just fine.
编辑:只是为了您的信息,我已经将mm / dd / YYYY HH:MM:ss.MMM格式字符串导入到该字段中。
3 个解决方案
#1
2
It depends on the "locale" date format, I guess.
我想这取决于“语言环境”日期格式。
Some samples on how to convert here : http://www.java2s.com/Code/SQLServer/Date-Timezone/Formatdatemmddyyyy.htm
有关如何转换的一些示例:http://www.java2s.com/Code/SQLServer/Date-Timezone/Formatdatemmddyyyy.htm
Hope this was helpful.
希望这很有帮助。
#2
0
Try issuing SET DATEFORMAT ydm; before INSERT. Then CONVERT(DATETIME,('2010/19/10 04:38:12.892')); works fine.
尝试发出SET DATEFORMAT ydm;在INSERT之前。然后CONVERT(DATETIME,('2010/19/10 04:38:12.892'));工作正常。
#3
0
You can try this:
你可以试试这个:
SET DATEFORMAT YDM;
select cast('2010/19/10 04:38:12.892' as datetime)
And it will parse it correctly
它会正确解析它
UPDATE: I found help here.
更新:我在这里找到了帮助。
But I tried casting to datetime2 directly and it didn't work. I don't understand why you can cast to datetime and not datetime2. Perhaps a good question for SO. :)
但我尝试直接转换到datetime2并且它不起作用。我不明白为什么你可以投射到datetime而不是datetime2。也许这是一个很好的问题。 :)
#1
2
It depends on the "locale" date format, I guess.
我想这取决于“语言环境”日期格式。
Some samples on how to convert here : http://www.java2s.com/Code/SQLServer/Date-Timezone/Formatdatemmddyyyy.htm
有关如何转换的一些示例:http://www.java2s.com/Code/SQLServer/Date-Timezone/Formatdatemmddyyyy.htm
Hope this was helpful.
希望这很有帮助。
#2
0
Try issuing SET DATEFORMAT ydm; before INSERT. Then CONVERT(DATETIME,('2010/19/10 04:38:12.892')); works fine.
尝试发出SET DATEFORMAT ydm;在INSERT之前。然后CONVERT(DATETIME,('2010/19/10 04:38:12.892'));工作正常。
#3
0
You can try this:
你可以试试这个:
SET DATEFORMAT YDM;
select cast('2010/19/10 04:38:12.892' as datetime)
And it will parse it correctly
它会正确解析它
UPDATE: I found help here.
更新:我在这里找到了帮助。
But I tried casting to datetime2 directly and it didn't work. I don't understand why you can cast to datetime and not datetime2. Perhaps a good question for SO. :)
但我尝试直接转换到datetime2并且它不起作用。我不明白为什么你可以投射到datetime而不是datetime2。也许这是一个很好的问题。 :)