asked on

re-converting dateparts to a single date time

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

rushShah

try this,

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

amillyard

ASKER

Hi rushShah, am getting: Incorrect syntax near ','. (after the datepart(day,SetupTime), )

ASKER CERTIFIED SOLUTION

TempDBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amillyard

ASKER

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

amillyard

ASKER

TempDBA: that works great :-) many thanks