秘密日期(varchar)到日期(Datetime) [英] Covert date (varchar) to date (Datetime)
问题描述
我需要以下内容的帮助.我在暂存表中的日期"列中包含以下
数据类型= varchar(50)
格式/样式=''dd/mm/yy''
例子=''18/11/07''
我需要将日期"列转换为以下内容.此转换将从登台表转换为目标表.
数据类型=日期时间
格式/样式=''mm/dd/yy''
例子=''11/18/07''
我已经尝试过以下查询
Hi,
I need help on the following.I have a column ''Date'' in Staging table with the following
Data type=varchar(50)
Format/Style=''dd/mm/yy''
Example=''18/11/07''
I need to convert ''Date'' column to the following .This conversion would take place from a staging table to a destination table.
Data type = datetime
Format/Style =''mm/dd/yy''
Example=''11/18/07''
I have tried the following query
SELECT Date,
CONVERT(varchar(50),Date,101)
FROM staging_table
但没有发生任何变化.我在哪里出错?
BUT NOTHING CHANGED .Where am I going wrong ?
推荐答案
Rachana,
这是因为您的SQL设置为其他格式.现在您的Convert具有输入varchar和输出varchar,因此转换不正确.
首先,您必须将其转换为日期时间,然后将其转换为适当的日期格式.见下文
Rachana,
This is because your SQL is set in different format. Now you Convert has input varchar and output also varchar, hence it is not converting correctly.
First you have to cast it to datetime and then convert it to appropriate date format. See below
set dateformat dmy
select convert(varchar(50),cast('18/11/07' as DateTime),101)
希望能有所帮助.如果是这样,请将答案标记为解决方案和/或投票.
-Milind
Hope that helps. If it does, mark the answer as solution and/or upvote.
-Milind
您基本上需要
You basically need to
insert into destinationtable select from staging table.
从我收集到的东西中
from what I gather this
SELECT Date,
CONVERT(varchar(50),Date,101)
FROM staging_table
将其转换为 varchar(50),您需要将暂存表中的char转换为 datetime .
Will convert it to varchar(50), you need to convert the char from the staging table to a datetime.
SELECT Date,
CONVERT(datetime,Date,101)
FROM staging_table
或类似的东西.
(很抱歉,如果语法不是100%正确,则我没有要测试的SQL Server.)
希望这会有所帮助.
PS:IMO,这不是C#问题,而是SQL/数据库问题.
or something similar.
(Apologies if the syntax is not 100% correct, I have no SQL-Server to test with.)
Hope this helps.
PS: IMO this is not a C# question, but an SQL/Database question.
SELECT convert(varchar,getdate(),101)AS日期-日期是临时列名
SELECT convert(varchar, getdate(), 101) AS Date -- Date is temporary Column name
这篇关于秘密日期(varchar)到日期(Datetime)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!