SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Assistance in converting INT into date.


Assistance in converting INT into date.

Author
Message
patrick.palmer
patrick.palmer
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 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')
Terry300577
Terry300577
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2351 Visits: 516
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?
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)SSC Guru (168K reputation)

Group: General Forum Members
Points: 168521 Visits: 39529
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);




Cool
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)
writearjun53
writearjun53
SSC-Addicted
SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)SSC-Addicted (434 reputation)

Group: General Forum Members
Points: 434 Visits: 227
CONVERT(VARCHAR(10), cast(StartDtID AS datetime), 101)


Regards,
Arjun
patrick.palmer
patrick.palmer
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 15
The filed has multiple dates in it. The 20050307 was just an example. Now all the dates say 2005-03-07???
Terry300577
Terry300577
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2351 Visits: 516
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?
patrick.palmer
patrick.palmer
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 15
The vendor will not give us access to the live data....
Terry300577
Terry300577
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2351 Visits: 516
fair enough. then the code ive supplied will work a treat if thats the column name for the real date
patrick.palmer
patrick.palmer
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 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')
Terry300577
Terry300577
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2351 Visits: 516
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')


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search