Solved

re-converting dateparts to a single date time

Posted on 2011-09-28
5
213 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…

732 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