SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Convert datetime
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

chipembele
Posting Yak Master

106 Posts

Posted - 02/17/2014 :  07:52:16  Show Profile  Reply with Quote
Hello
I have a problem where we have a datetime field called Date that returns this

2013-09-16 00:00:00

What I need to do is convert this so it returns like this

16-09-2013

But, the format still needs to be DateTime

Then,I have a DateTime field called StartTime that returns 2013-09-16 10:45:00 and I need to return just 10:45 and it still be DateTime

THen I need to in someway link both the fields together so one field looks like this

16-09-2013 10:45 and still be DateTime format

Is this possible? I dont think it is but then I only know enough SQL to get by.

Any guidance much appreciated

Dan

stepson
Constraint Violating Yak Guru

Romania
434 Posts

Posted - 02/17/2014 :  08:09:03  Show Profile  Reply with Quote
I think, this is best done in client application

but here is a sample:


declare 
	@dtD as datetime
	,@dtStartTime  as datetime

set @dtD='2013-09-16 00:00:00'--'2013-09-16 10:45:00'
set @dtStartTime='2013-09-16 10:45:00'

select CONVERT(VARCHAR(30),@dtD,105)
	,CONVERT(VARCHAR(30),@dtStartTime,108)
	,CONVERT(VARCHAR(30),@dtD,105)  + ' ' + CONVERT(VARCHAR(30),@dtStartTime,108)
	,CONVERT(DATETIME,CONVERT(VARCHAR(30),@dtD,105)  + ' ' + CONVERT(VARCHAR(30),@dtStartTime,108),105)



S


sabinWeb MCP
Go to Top of Page

chipembele
Posting Yak Master

106 Posts

Posted - 02/17/2014 :  08:12:49  Show Profile  Reply with Quote
Thanks

I'll have a look at it. It wouldnt necessarily be those numbers everytime so would need to be generic.
Go to Top of Page

wided
Posting Yak Master

200 Posts

Posted - 02/18/2014 :  06:23:00  Show Profile  Reply with Quote
what abount this

if i want to have this format:

16092013
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
169 Posts

Posted - 02/18/2014 :  06:48:36  Show Profile  Reply with Quote
SELECT CAST(DATEPART(DD,GETDATE())AS VARCHAR(10))+'-'+CAST(DATEPART(MM,GETDATE()) AS VARCHAR(20))+'-'+CAST(DATEPART(YY,GETDATE()) AS VARCHAR(20))

SELECT REPLACE(CONVERT(VARCHAR,GETDATE(),104),'.','-')

Veera

Edited by - VeeranjaneyuluAnnapureddy on 02/18/2014 06:51:29
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
434 Posts

Posted - 02/18/2014 :  06:48:42  Show Profile  Reply with Quote
quote:
Originally posted by wided

what abount this

if i want to have this format:

16092013





declare 
	@dtD as datetime
	

set @dtD='2013-09-16 00:00:00'--'2013-09-16 10:45:00'

select REPLACE(CONVERT(varchar(30),@dtD,104),'.','')



--output

16092013



sabinWeb MCP
Go to Top of Page

wided
Posting Yak Master

200 Posts

Posted - 02/18/2014 :  07:08:56  Show Profile  Reply with Quote
ok
thanks
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 02/25/2014 :  09:04:40  Show Profile  Reply with Quote
Replace the GetDate() with your date fields and change the FROM to your DB.

Select Convert(Varchar(10), Getdate(),105)
+ ' ' + Convert(Varchar(8), GetDate(),114) as DateTimeCombo
FROM YOURDB

SZ1
Please help me to enable me to help others!
Go to Top of Page

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 02/25/2014 :  14:01:18  Show Profile  Reply with Quote
SELECT FORMAT(Date,'dd-MM-yyyy')+' '+FORMAT(Startime,'hh:mm') FROM table_name


--------------------
!_(M)_!
Go to Top of Page

chipembele
Posting Yak Master

106 Posts

Posted - 02/26/2014 :  04:31:21  Show Profile  Reply with Quote
Hi
Who are these new ones for please? I'm lost now with wided gatecrashing my thread. I understand its the same problem but I feel a new thread of his own should have been started.
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 02/26/2014 :  04:45:16  Show Profile  Reply with Quote
yes he should have created a new topic...but as I said this will add your 2 fields together stripping only the date and time parts from each field.

So,

Select Convert(Varchar(10), AddYourFirstDateTimeHere),105)
+ ' ' + Convert(Varchar(8), AddYourSecondDateTimeHere),114) as DateTimeResult
FROM YOURDB

Also, Im assuming the 2 DateTime fields return the same result set although they are in different formats?

If this is the case then you can use the above SQL.
105 will strip the Date Part
114 will strip the Time Part
+ ' ' + will string them together as one field.

If you will it you can achieve it!!
Go to Top of Page

chipembele
Posting Yak Master

106 Posts

Posted - 02/26/2014 :  05:42:27  Show Profile  Reply with Quote
Thanks sz1
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000