将DateTime整数转换为datetime [英] Converting DateTime integer to datetime

问题描述

我有两个cloumns 1)日期2)时间.
在日期列中，值存储为"20120125"
在时间列中，值存储为"070550"
我需要的是将这些列值组合在一起，然后插入到一个名为used_date的目标datetime列中.
表示应采用以下格式
''2012-01-25 07:05:50.000''-----> used_date
该怎么做??

在此先谢谢您.

Hi,
I have two cloumns 1)date 2)time.
In date column the value is stored as ''20120125''
In time column the value is stored as ''070550''
What I need is, to combine these column values and insert into one target datetime column named used_date.
Means It should be in the following format
''2012-01-25 07:05:50.000'' ----->used_date
How to do this??

Thanks in Advance.

推荐答案

我希望这可以为您提供有关如何做到这一点的想法:

I hope this give you the idea of how to do that :

declare @date varchar(max)
set @date= '20120125'

declare @time varchar(max)
set @time= '070550'

declare @dt as datetime
set @dt = CAST (stuff(stuff(@date, 5, 0,'/'), 8, 0, '/')+' '+ stuff(stuff(@time, 3, 0,':'), 6, 0, ':') as datetime )

print @dt

Stuff 文档:
http://msdn.microsoft.com/en-us/library/ms188043.aspx [ ^ ]

希望对您有所帮助.

Stuff documentation :
http://msdn.microsoft.com/en-us/library/ms188043.aspx[^]

Hope it helps.

谢谢您的提问.您可以按照下面的代码.

Thank you for your question. You can follow the bellow code.

DECLARE @FormatedDateTime varchar(36)
DECLARE @YourDate char(10)
DECLARE @YourTime char(10)

SELECT @YourDate = '101114'
SELECT @YourTime = '162941'

SELECT @FormatedDateTime = convert(varchar, convert(datetime, @YourDate), 111)
    + ' ' + substring(@YourTime, 1, 2)
    + ':' + substring(@YourTime, 3, 2)
    + ':' + substring(@YourTime, 5, 2)

SELECT FormattedDateTime = @FormatedDateTime

Thanks,
Mamun

如果您使用的是SQL Server 2008，则可以合并以下列:

If you''re using SQL Server 2008 then you can combine the columns like this:

SELECT CAST(CAST(your_date_column AS DATE) AS DATETIME) +
    CAST(your_time_column AS TIME)
FROM your_table

对于SQL Server的早期版本，这是您需要的:

For earlier versions of SQL Server then this is what you''ll need:

SELECT DATEADD(day, 0, DATEDIFF(day, 0, your_date_column)) +
    DATEADD(day, 0 - DATEDIFF(day, 0, your_time_column), your_time_column)
FROM your_table

也尝试以下一项:

Try this one also:

Convert(datetime, Convert(char(10), MYDATETIMEFIELD, 103) + '' '' + Convert(char(8), MYTIMEFIELD, 108), 103) 

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