Solved

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

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

820 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