Wednesday, May 15, 2013

Convert a SQL query result table to an HTML table for email 

declare @body varchar(max)

set @body = cast( (
select td = dbtable + '' + cast( entities as varchar(30) ) + '' + cast( rows as varchar(30) )

from (

select dbtable  = object_name( object_id ),

entities = count( distinct name ),

rows     = count( * )

from sys.columns

group by object_name( object_id )

) as d

for xml path( 'tr' ), type ) as varchar(max) )



set @body = ''           + '

'           + replace( replace( @body, '<', '<' ), '>', '>' )           + '
Database TableEntity CountTotal Rows
'



print @body

 <html>
   <head >
   </head>
   <body>
   <body>
 <html>

http://stackoverflow.com/questions/7070053/convert-a-sql-query-result-table-to-an-html-table-for-email
Posted by Louis at 12:28 PM 2 comments
Labels:

SQL Cast and Convert Date

Using SQL Server 2008
This first option of removing the date uses the SQL Server 2008 method. This will return only the date or only the time:

-- remove the time
SELECT CAST(GETDATE() AS DATE) -- 2009-07-12
-- remove the date
SELECT CAST(GETDATE() AS TIME) -- 08:46:25.8130000
If you’re not working with SQL Server 2008, you have to improvise.

Cast Date With No Time Using Floor
This example removes the time from the date time by setting it to the beginning of the day.

-- Get the current day with no time
-- 2009-06-29 00:00:00.000
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS datetime)

-- Get the next day
-- 2009-06-30 00:00:00.000
SELECT CAST(CEILING (CAST(GETDATE() AS FLOAT)) AS datetime)
Cast Date with No Time Using Convert
Using convert you can remove the time for display purposes, usually for reporting or the front end.

-- Month first
SELECT CONVERT(VARCHAR(12),GETDATE(), 101) -- 06/29/2009
SELECT CONVERT(VARCHAR(12),GETDATE(), 110) -- 06-29-2009
SELECT CONVERT(VARCHAR(12),GETDATE(), 100) -- Jun 29 2009
SELECT CONVERT(VARCHAR(12),GETDATE(), 107) -- Jun 29, 2009

-- Year first
SELECT CONVERT(VARCHAR(12),GETDATE(), 102) -- 2009.06.29
SELECT CONVERT(VARCHAR(12),GETDATE(), 111) -- 2009/06/29
SELECT CONVERT(VARCHAR(12),GETDATE(), 112) -- 20090629

-- Day first
SELECT CONVERT(VARCHAR(12),GETDATE(), 103) -- 29/06/2009
SELECT CONVERT(VARCHAR(12),GETDATE(), 105) -- 29-06-2009
SELECT CONVERT(VARCHAR(12),GETDATE(), 104) -- 29.06.2009
SELECT CONVERT(VARCHAR(12),GETDATE(), 106) -- 29 Jun 2009

-- Time only
SELECT CONVERT(VARCHAR(12),GETDATE(), 108) -- 07:26:16
SELECT CONVERT(VARCHAR(12),GETDATE(), 114) -- 07:27:11:203
Roll Your Own Date
To roll your own date format, use the datename function. This is also the only way to get the full month name.

SELECT DATENAME(MONTH, GETDATE()) -- June
SELECT DATENAME(DAY,GETDATE()) -- 29
SELECT DATENAME(YEAR, GETDATE()) -- 2009

-- Concatente values
-- June.29.2009
SELECT DATENAME(MONTH, GETDATE()) + '.' + DATENAME(DAY,GETDATE()) + '.' + DATENAME(YEAR, GETDATE())
Here are the rest of the possible datepart intervals:

Date Part Name Interval Values
Year year, yyyy, yy
Quarter quarter, q, qq
Month month, mm, mm
Day of the year dayofyear, dy, y
Day day, dd,. D
Weekday weekday, dw
Week week, wk, ww
Hour hour, hh
Minute minute, mi, n
Second second, ss, s

http://sqlserverplanet.com/tsql/cast-date
Posted by Louis at 12:26 PM 0 comments
Labels:
Subscribe to: Posts (Atom)