Solved

re-converting dateparts to a single date time

Posted on 2011-09-28
5
209 Views
Last Modified: 2012-05-12
I have split a date into 3 parts (day, month & year).

Currently I have these displayed (via select statement) as x3 columns.

How do I convert this back to a single column (a full datetime column entry) ?
,datepart(day,SetupTime) as 'DAY' 
,datepart(month,SetupTime) as 'MTH'
,datepart(year,SetupTime) as 'YEAR'

Open in new window

0
Comment
Question by:amillyard
  • 3
5 Comments
 
LVL 8

Expert Comment

by:rushShah
ID: 36715131
try this,

SELECT Convert(DateTime, datepart(month,SetupTime) , datepart(day,SetupTime),
datepart(year,SetupTime) )
0
 

Author Comment

by:amillyard
ID: 36715191
Hi rushShah,  am getting:  Incorrect syntax near ','.  (after the datepart(day,SetupTime), )
0
 
LVL 25

Accepted Solution

by:
TempDBA earned 500 total points
ID: 36715230

SELECT DATEPART(DAY,SetupTime),
DATEPART(MONTH,SetupTime),
DATEPART(YEAR,SetupTime),
CONVERT(DATETIME,(CAST(DATEPART(MONTH,SetupTime) AS VARCHAR)  +'-' + CAST(DATEPART(DAY,SetupTime) AS VARCHAR) + '-' + CAST(DATEPART(YEAR,SetupTime) AS VARCHAR)) )

0
 

Author Comment

by:amillyard
ID: 36715244
this is the scripting context applying your suggestion
USE [db1]

DECLARE @endDate   datetime
DECLARE @startDate datetime

SET @startDate = '2011-09-01 00:00:00.000'
SET @endDate   = '2011-09-30 23:59:59.999'
	    
select cast( Cast(isnull(SUM(ChargeableAmount), 0) AS MONEY) / 100000 AS MONEY) AS 'TOTAL'
	   ,Convert(DateTime, datepart(month,SetupTime) , datepart(day,SetupTime),
datepart(year,SetupTime)
   
from [CDRData] 

WHERE (Direction = 1) 
	AND (COSID =95) 
	AND (SetupTime BETWEEN @startDate AND @endDate)
      
group by datepart(day,SetupTime), datepart(month,SetupTime), datepart(year,SetupTime)
order by datepart(day,SetupTime), datepart(month,SetupTime), datepart(year,SetupTime)

Open in new window

0
 

Author Closing Comment

by:amillyard
ID: 36715316
TempDBA:  that works great :-)  many thanks
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Detach & Attach 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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

747 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

13 Experts available now in Live!

Get 1:1 Help Now