Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Need to pad string values
Message
 
 
To
07/12/2001 09:56:05
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Miscellaneous
Thread ID:
00591126
Message ID:
00591153
Views:
25
This message has been marked as the solution to the initial question of the thread.
You can use MS SQL Server specific function CONVERT for a better control over conversion
SELECT
   Convert( char(10), [Interval], 102) AS Dates, 
   Replace(Convert( char(10), [Interval], 108), ':', '.') AS Times
FROM dbo.Detail
or you can modify your code as follow
SELECT DISTINCT TOP 100 PERCENT Member_Id,
	REPLACE( CAST(DATEPART(yyyy, [Interval]) AS char(4))
		+ '.' + CAST(DATEPART(mm, [Interval]) AS char(2))
		+ '.' + CAST(DATEPART(dd, [Interval]) AS char(2)), ' ', '0') AS Dates,
	REPLACE( CAST(DATEPART(hh, [Interval]) AS char(2))
		+ '.' + CAST(DATEPART(mi,[Interval]) AS char(2))
		+ '.' + CAST(DATEPART(ss, [Interval]) AS char(2)), ' ', '0') AS Times
FROM dbo.Detail
>In the table there's a datetime column.
>
>The following syntax converts this to two char columns, and it works fine, and it's fast.
>
>
>SELECT DISTINCT TOP 100 PERCENT Member_Id,
>   RTRIM(CAST(DATEPART(yyyy, [Interval]) AS char))
>   + '.' + RTRIM(CAST(DATEPART(mm, [Interval]) AS char))
>   + '.' + RTRIM(CAST(DATEPART(dd, [Interval]) AS char)) AS Dates,
>   RTRIM(CAST(DATEPART(hh, [Interval]) AS char))
>   + '.' + RTRIM(CAST(DATEPART(mi,[Interval]) AS char))
>   + '.' + RTRIM(CAST(DATEPART(ss, [Interval]) AS char)) AS Times
>FROM dbo.Detail
>
--sb--
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform