Solved

how to get datetime format from yyy-dd-mm  + time part

Posted on 2013-01-09
1
666 Views
Last Modified: 2013-01-10
	SELECT @thedatetime	= getdate()
	SELECT @thedate = STR(Datepart(yyyy, @thedatetime), 4) + STR(Datepart(mm, @thedatetime), 2) + STR(Datepart(dd, @thedatetime), 2)
	SELECT @thetime = STR(Datepart(hh, @thedatetime), 2) + STR(Datepart(mi, @thedatetime), 2) + STR(Datepart(ss, @thedatetime), 2)
	SELECT @thedate = REPLACE(@thedate, ' ', '0')
	SELECT @thetime = REPLACE(@thetime, ' ', '0')

Open in new window



thedate and thetime will be stored in my table column each one in a column i want to get the datetime format yyy-dd-mm hh:mm:ss.XXX
from the two field
0
Comment
Question by:afifosh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38762023
This is a little clunky, and there may be a more efficient way to do it, but this works.

There's no standard converting from the time format in the standard case and convert formats (see documentation: http://msdn.microsoft.com/en-us/library/ms187928.aspx)

I took your code and then converted backwards to the original date and time:

declare @thedatetime datetime
declare @thedate char(20)
declare @thetime char(20)
declare @newdate datetime
declare @newtime datetime
declare @finaldatetime datetime

SELECT @thedatetime	= getdate()
SELECT @thedate = STR(Datepart(yyyy, @thedatetime), 4) + STR(Datepart(mm, @thedatetime), 2) + STR(Datepart(dd, @thedatetime), 2)
SELECT @thetime = STR(Datepart(hh, @thedatetime), 2) + STR(Datepart(mi, @thedatetime), 2) + STR(Datepart(ss, @thedatetime), 2)
SELECT @thedate = REPLACE(@thedate, ' ', '0')
SELECT @thetime = REPLACE(@thetime, ' ', '0')
              
print '1: '+convert(char(30),@thedatetime)
print '2: '+convert(char(30),@thedate)
print '3: '+convert(char(30),@thetime)

select @newdate = convert (datetime, @thedate, 112);
select @newtime = convert (datetime, substring(@thetime,1,2)+':'+substring(@thetime,3,2)+':'+substring(@thetime,1,2), 114);

print '4: '+convert(char(30),@newdate)
print '5: '+convert(char(30),@newtime)

select @finaldatetime = @newdate + @newtime;

print '6: '+convert(char(30),@finaldatetime)

Open in new window


Outputs from the 6 print statements:

1: Jan 10 2013  1:07AM           - Start datetime
2: 20130110                      - Start date
3: 010712                        - Start Time
4: Jan 10 2013 12:00AM           - End result, just the date
5: Jan  1 1900  1:07AM           - End result, just the time
6: Jan 10 2013  1:07AM           - Date and time together

Open in new window


Then, when you have it back in datetime format, you could use this convert to show the milliseconds:

select convert(char(30), @finaldatetime, 121)

Which would show:

2013-01-10 01:07:12.000
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question