Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Assistance in converting INT into date. Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 7:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 25, 2013 8:33 AM
Points: 8, Visits: 15
I have a query that has two date fields that are Int....20050307. I need to convert them to date fields. 03/07/2005 I can not seem to get them to convert. Can anyone out there offer me some assistance please. The fields are fact.StartDtID and fact.EndDtId. See query below and Thank you in advance.
SELECT
dimUser.EmpFullName1 [User Name],
dimUser.MgrEmpFullName1 AS Manager,
dimUser.PrimaryJobName AS [Primary Job],
dimUser.EmpNo AS [User Number],
CONVERT(VARCHAR(10), fact.StartDtID, 101) [Start Date],
CONVERT(VARCHAR(10),fact.EndDtID, 101) [Completion Date],
dimUser.PrimaryOrgCode,
dimUser.SecondaryOrg1 AS [Secondary Org 1],
dimUser.SecondaryOrg2 AS [Secondary Org 2],
dimUser.PrimaryOrgName AS Org,
fact.EndDtID,
dimUser.EmpStat AS [Emp Status],
CASE WHEN fact.completionStatusID = '-1'
THEN 'Not Satisfied'
ELSE 'Satisfied' END AS Status
FROM factAttempt AS fact
INNER JOIN dimActivity AS A ON A.ID = fact.ActivityID
INNER JOIN dimUser ON dimUser.ID = fact.UserID
INNER JOIN dimDate AS DD ON DD.DateID = fact.EndDtID
WHERE (dimUser.EmpStat = 'active')
AND (a.code = 'A2102')
AND (fact.StartDtID >= '20130101')
Post #1434891
Posted Monday, March 25, 2013 7:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:01 AM
Points: 1,726, Visits: 486
can you join to the dim.date table twice once for each date and then return the actual date, which i'd expect to be stored in most date dimension tables?
Post #1434895
Posted Monday, March 25, 2013 7:24 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:33 PM
Points: 22,475, Visits: 30,157
patrick.palmer (3/25/2013)
I have a query that has two date fields that are Int....20050307. I need to convert them to date fields. 03/07/2005 I can not seem to get them to convert. Can anyone out there offer me some assistance please. The fields are fact.StartDtID and fact.EndDtId. See query below and Thank you in advance.
SELECT
dimUser.EmpFullName1 [User Name],
dimUser.MgrEmpFullName1 AS Manager,
dimUser.PrimaryJobName AS [Primary Job],
dimUser.EmpNo AS [User Number],
CONVERT(VARCHAR(10), fact.StartDtID, 101) [Start Date],
CONVERT(VARCHAR(10),fact.EndDtID, 101) [Completion Date],
dimUser.PrimaryOrgCode,
dimUser.SecondaryOrg1 AS [Secondary Org 1],
dimUser.SecondaryOrg2 AS [Secondary Org 2],
dimUser.PrimaryOrgName AS Org,
fact.EndDtID,
dimUser.EmpStat AS [Emp Status],
CASE WHEN fact.completionStatusID = '-1'
THEN 'Not Satisfied'
ELSE 'Satisfied' END AS Status
FROM factAttempt AS fact
INNER JOIN dimActivity AS A ON A.ID = fact.ActivityID
INNER JOIN dimUser ON dimUser.ID = fact.UserID
INNER JOIN dimDate AS DD ON DD.DateID = fact.EndDtID
WHERE (dimUser.EmpStat = 'active')
AND (a.code = 'A2102')
AND (fact.StartDtID >= '20130101')




-- SQL Server 2008 answer:

declare @MyDate int = 20050307;

select cast(cast(@MyDate as varchar(8)) as date);





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1434897
Posted Monday, March 25, 2013 7:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 11:36 PM
Points: 41, Visits: 220
CONVERT(VARCHAR(10), cast(StartDtID AS datetime), 101)


Regards,
Arjun
Post #1434905
Posted Monday, March 25, 2013 7:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 25, 2013 8:33 AM
Points: 8, Visits: 15
The filed has multiple dates in it. The 20050307 was just an example. Now all the dates say 2005-03-07???
Post #1434906
Posted Monday, March 25, 2013 7:40 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:01 AM
Points: 1,726, Visits: 486
without knowing the columns on your Date Dimension table i assume its called ActualDate, here is a solution which doesnt require any conversion, since it should of already been done for you.

SELECT  dimUser.EmpFullName1 [User Name]
, dimUser.MgrEmpFullName1 AS Manager
, dimUser.PrimaryJobName AS [Primary Job]
, dimUser.EmpNo AS [User Number]
, CONVERT(VARCHAR(10), fact.StartDtID, 101) [Start Date]
, CONVERT(VARCHAR(10), fact.EndDtID, 101) [Completion Date]
, dimUser.PrimaryOrgCode
, dimUser.SecondaryOrg1 AS [Secondary Org 1]
, dimUser.SecondaryOrg2 AS [Secondary Org 2]
, dimUser.PrimaryOrgName AS Org
--, fact.EndDtID--Removed
,sd.ActualDate AS StartDate --Added
,dd.ActualDate AS EndDate --Added
, dimUser.EmpStat AS [Emp Status]
, CASE WHEN fact.completionStatusID = '-1' THEN 'Not Satisfied'
ELSE 'Satisfied'
END AS Status
FROM factAttempt AS fact
INNER JOIN dimActivity AS A ON A.ID = fact.ActivityID
INNER JOIN dimUser ON dimUser.ID = fact.UserID
INNER JOIN dimDate AS DD ON DD.DateID = fact.EndDtID
INNER JOIN dimDate AS sd ON DD.DateID = fact.StartDtID --Added
WHERE (dimUser.EmpStat = 'active')
AND (a.code = 'A2102')
AND (fact.StartDtID >= '20130101')



Additionally , this is obviously the data for a cube, why aren't you using the cube to produce this result which would be even simpler?
Post #1434909
Posted Monday, March 25, 2013 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 25, 2013 8:33 AM
Points: 8, Visits: 15
The vendor will not give us access to the live data....
Post #1434912
Posted Monday, March 25, 2013 7:44 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:01 AM
Points: 1,726, Visits: 486
fair enough. then the code ive supplied will work a treat if thats the column name for the real date
Post #1434914
Posted Monday, March 25, 2013 7:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 25, 2013 8:33 AM
Points: 8, Visits: 15
Now I Get this:
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the nvarchar value '11/13/1964' to data type int.


SELECT
dimUser.EmpFullName1 [User Name],
dimUser.MgrEmpFullName1 AS Manager,
dimUser.PrimaryJobName AS [Primary Job],
dimUser.EmpNo AS [User Number],
CONVERT(VARCHAR(10), fact.StartDtID, 101) [Start Date],
CONVERT(VARCHAR(10),fact.EndDtID, 101) [Completion Date],
dimUser.PrimaryOrgCode,
dimUser.SecondaryOrg1 AS [Secondary Org 1],
dimUser.SecondaryOrg2 AS [Secondary Org 2],
dimUser.PrimaryOrgName AS Org,
sd.DateDesc AS StartDate,
dd.DateDesc AS EndDate,
dimUser.EmpStat AS [Emp Status],
CASE WHEN fact.completionStatusID = '-1'
THEN 'Not Satisfied'
ELSE 'Satisfied' END AS Status
FROM factAttempt AS fact
INNER JOIN dimActivity AS A ON A.ID = fact.ActivityID
INNER JOIN dimUser ON dimUser.ID = fact.UserID
INNER JOIN dimDate AS DD ON DD.DateID = fact.EndDtID
JOIN dimDate AS SD on sD.DateDesc = fact.StartDtID
WHERE (dimUser.EmpStat = 'active')
Post #1434918
Posted Monday, March 25, 2013 7:51 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:01 AM
Points: 1,726, Visits: 486
Here you go

the problem was on your join

        SELECT  dimUser.EmpFullName1 [User Name]
, dimUser.MgrEmpFullName1 AS Manager
, dimUser.PrimaryJobName AS [Primary Job]
, dimUser.EmpNo AS [User Number]
, CONVERT(VARCHAR(10), fact.StartDtID, 101) [Start Date]
, CONVERT(VARCHAR(10), fact.EndDtID, 101) [Completion Date]
, dimUser.PrimaryOrgCode
, dimUser.SecondaryOrg1 AS [Secondary Org 1]
, dimUser.SecondaryOrg2 AS [Secondary Org 2]
, dimUser.PrimaryOrgName AS Org
, sd.DateDesc AS StartDate
, dd.DateDesc AS EndDate
, dimUser.EmpStat AS [Emp Status]
, CASE WHEN fact.completionStatusID = '-1' THEN 'Not Satisfied'
ELSE 'Satisfied'
END AS Status
FROM factAttempt AS fact
INNER JOIN dimActivity AS A ON A.ID = fact.ActivityID
INNER JOIN dimUser ON dimUser.ID = fact.UserID
INNER JOIN dimDate AS DD ON DD.DateID = fact.EndDtID
JOIN dimDate AS SD ON sD.DateID = fact.StartDtID
WHERE (dimUser.EmpStat = 'active')

Post #1434923
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse