Convert datetime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Convert datetime

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2014-02-17 : 07:52:16

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
Aged Yak Warrior

545 Posts

Posted - 2014-02-17 : 08:09:03

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

chipembele
Posting Yak Master

106 Posts

Posted - 2014-02-17 : 08:12:49

Thanks

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

wided
Posting Yak Master

218 Posts

Posted - 2014-02-18 : 06:23:00

what abount this

if i want to have this format:

16092013

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-02-18 : 06:48:36

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

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-18 : 06:48:42

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

wided
Posting Yak Master

218 Posts

Posted - 2014-02-18 : 07:08:56

ok
thanks

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-02-25 : 09:04:40

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!

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-02-25 : 14:01:18

SELECT FORMAT(Date,'dd-MM-yyyy')+' '+FORMAT(Startime,'hh:mm') FROM table_name

--------------------
!_(M)_!

chipembele
Posting Yak Master

106 Posts

Posted - 2014-02-26 : 04:31:21

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.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-02-26 : 04:45:16

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!!

chipembele
Posting Yak Master

106 Posts

Posted - 2014-02-26 : 05:42:27

Thanks sz1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources