June 4, 2013 at 6:42 am
Hi,
I have a field (called 'perControl') that contains a date value, plus one character. But the value is formatted like this:
201301042
I need to remove the last character (last '2' in the above example) but I also need to format the left over value (20130104) to a date value:
01/04/2013
How can I do this?
June 4, 2013 at 6:45 am
select cast(left('201301042',8) as date)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 4, 2013 at 6:47 am
thanks Phil
I get a:
Type date is not a defined system type error
Here's the code:
cast(left('pehPErcontrol',8) as date),
June 4, 2013 at 6:51 am
Ah - sorry - that came in a later version of SQL Server.
Try 'datetime' instead of 'date'.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 4, 2013 at 6:52 am
The data type DATE is not available in SQL Server 2005 and hence, the error
Change it to DATETIME or SMALLDATETIME
select cast(left('201301042',8) as datetime)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 4, 2013 at 6:52 am
changed it to datetime and got:
Conversion failed when converting datetime from character string
cast(left('pehPErcontrol',8) as datetime),
June 4, 2013 at 6:56 am
krypto69 (6/4/2013)
changed it to datetime and got:Conversion failed when converting datetime from character string
cast(left('pehPErcontrol',8) as datetime),
Is there a chance that any of the values in pehPErcontrol is not a valid date?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 4, 2013 at 6:58 am
You probably have some data which is not a valid date
SELECT*
FROMYourTableName
WHEREISDATE(LEFT(perControl,8)) = 0
The above code will give you the list of invalid dates.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 4, 2013 at 7:00 am
Umm... I don't think there's a chance. But I wouldn't bet my life on it..
They are all dates, but with an extra character on the end.
If I change the query to 'TOP 1' I still get the error.
Here's the whole statement (hope this helps)
/* Summary Overtime by Employee */
SELECT
--pehPerControl,
--SUBSTRING(pehPErcontrol,1, 8) AS [Period Control Date],
cast(left('pehPErcontrol',8) as datetime),
cmpCompanyName,
rtrim(eepNameLast) +
isnull(' '+rtrim(CodDesc), '') +
', ' + rtrim(eepNameFirst) +
' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name,
eepNameLast,
eepNameSuffix,
eepNameFirst,
eepNameMiddle,
eepSSN,
pehBatchID,
pehCurAmt,
pehCurHrs,
pehCoID,
pehEEID,
pehEmpNo,
pehLocation,
pehJobCode,
pehOrgLvl1,
pehOrgLvl2,
pehOrgLvl3,
pehOrgLvl4,
pehPayGroup,
pehProject,
pehShfShiftAmt,
COALESCE(cmpCompanyName+pehCoID, 'Not Specified' ) as Group1,
COALESCE(pehOrgLvl2, 'Not Specified' ) as Group2,
COALESCE('No Group3', 'Not Specified' ) as Group3,
COALESCE('No Group4', 'Not Specified' ) as Group4,
COALESCE('No Group5', 'Not Specified' ) as Group5,
COALESCE('No Group6', 'Not Specified' ) as Group6
FROM EmpPers (NOLOCK) JOIN pearhist (NOLOCK)
ON pehEEID = eepEEID
JOIN EmpComp (NOLOCK)
ON pehEEID = eecEEID
AND pehCoID = eecCoID
LEFT OUTER JOIN Codes (NOLOCK)
ON CodTable = 'SUFFIX'
AND CodCode = eepNameSuffix
AND eepNameSuffix <> 'Z'
JOIN Company (NOLOCK) ON pehCoID = cmpCoID
--WHERE pehPerControl between '201301011' AND '201305319'
WHERE pehPerControl > '201301011'
AND pehIsVoided <> 'Y'
AND pehIsVoidingRecord <> 'Y'
AND pehIsOvertime = 'Y'
--and eepnamefirst like '%rayna%'
--AND pehCoID in ('XRFL4')
--AND pehPayGroup = 'WSIHQ'
ORDER BY pehPerControl, CMPCOMPANYNAME+PEHCOID, PEHORGLVL2
June 4, 2013 at 7:02 am
I ran the statement:
SELECT*
FROMpearhist
WHEREISDATE(LEFT(pehPErcontrol,8)) = 0
And got zero records
June 4, 2013 at 8:02 am
Your issue is here.
cast(left('pehPErcontrol',8) as datetime),
You are trying to cast the string literal 'pehPErcontrol' instead of the column value to a datetime.
Try this instead.
cast(left(pehPErcontrol, 8) as datetime)
Also be very careful with all those NOLOCK hints. They can do very bad things to your results.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2013 at 8:22 am
Thanks Sean! Worked great.
Yeah I know about the (no lock)...the vender has these all over the place..
crazy I know..
Thanks everybody for helping!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply