Converting DateTime integer to datetime

Question

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 24-Jan-12 19:12pm

soubins

Updated 24-Jan-12 19:22pm

Prasad_Kulkarni

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Amir Mahfoozi · Answer 1 · 2012-01-24T22:26:00

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.

Abdul Quader Mamun · Answer 2 · 2012-01-24T22:39:00

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

Prasad_Kulkarni · Answer 3 · 2012-01-24T19:20:00

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)

Converting DateTime integer to datetime

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0