March 25, 2013 at 7:16 am
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')
March 25, 2013 at 7:22 am
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?
March 25, 2013 at 7:24 am
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);
March 25, 2013 at 7:34 am
CONVERT(VARCHAR(10), cast(StartDtID AS datetime), 101)
Regards,
Arjun
March 25, 2013 at 7:36 am
The filed has multiple dates in it. The 20050307 was just an example. Now all the dates say 2005-03-07???
March 25, 2013 at 7:40 am
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.ActualDateAS 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?
March 25, 2013 at 7:41 am
The vendor will not give us access to the live data....
March 25, 2013 at 7:44 am
fair enough. then the code ive supplied will work a treat if thats the column name for the real date
March 25, 2013 at 7:46 am
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')
March 25, 2013 at 7:51 am
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')
March 25, 2013 at 7:54 am
Perfect! Thank you so much. My SQL skills have just gotten better Cheers.
March 25, 2013 at 8:32 am
patrick.palmer (3/25/2013)
The filed has multiple dates in it. The 20050307 was just an example. Now all the dates say 2005-03-07???
The following was showing you a way to convert a date represented as an integer into a date:
-- SQL Server 2008 answer:
declare @MyDate int = 20050307;
select cast(cast(@MyDate as varchar(8)) as date);
It was up to you to figure out how to use it in your query.
March 25, 2013 at 8:59 am
Glad i could help.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply