Click here to Skip to main content
15,353,432 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 24-Jan-12 19:22pm
v2

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

SQL
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 documentation :
http://msdn.microsoft.com/en-us/library/ms188043.aspx[^]

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

SQL
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



VB
Thanks,
Mamun
   
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

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) 
   
v2
Comments
codeBegin 25-Jan-12 1:29am
   
My +5
Heino Zunzer 25-Jan-12 6:02am
   
have you tried any of these statements? Not one of them works!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900