Solved

re-converting dateparts to a single date time

Posted on 2011-09-28
5
210 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Filtered index 5 56
SQL Query 3 49
SQL Server - Check Constraint or NOT NULL? 11 69
How to write a sql query returning a result  with my date time template? 8 33
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 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

28 Experts available now in Live!

Get 1:1 Help Now