Solved

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 46
SQL server 2008 SP4 29 34
combine an MS SQL string in Idera DM 9 32
SQL Server 2012 Express to Full 5 25
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 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

12 Experts available now in Live!

Get 1:1 Help Now