November 17, 2011 at 9:14 am
Hi,
am doing this conversation ad am getting the following error. can any one correct me please....
CAST (Date) + ' ' +
LEFT (isnull(Time,''),2) + ':' +
RIGHT(isnull(Time,''),2) AS smalldatetime) AS Eventdate
from table1
error:
Conversion failed when converting character string to smalldatetime data type
Thanks.
November 17, 2011 at 9:26 am
Can you post the schema of the table and few records in it.
November 17, 2011 at 9:40 am
Iam having a date coulmn with datatype as varchar (8) and time coulmn with datatye varchar (4)
data:
date column: 20061025
time column : 1228
Thanks.
November 17, 2011 at 10:19 am
Try this
SELECT convert(varchar, convert(datetime, Date), 111)
+ ' ' + substring(Time, 1, 2)
+ ':' + substring(Time, 3, 2)
From TableName
If you need more examples on data conversions checkout this link http://www.sqlusa.com/bestpractices/datetimeconversion/
November 17, 2011 at 11:13 am
Thanks a lot,
It works good.
November 17, 2011 at 1:17 pm
And if at all possible store your data as datetime instead of multiple varchar columns. Dates as string is fraught with hair pulling data inconsistencies and conversion challenges.
_______________________________________________________________
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/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply