Solved

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

Posted on 2013-01-09
1
664 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

943 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now