SQL Server and MySQL Ramblings
22 August 2008 - Concatenating Row Values in Transact-SQL
Full article - Anith Sen wrote a great article on how to concatinate rows in Transact-SQL
I have had the need in the past to concatinate rows into one value and when I saw this article in Firdays SQLServerCentral newsletter, I knew it would be a good read. Anith goes into great detail, covering several approaches, even CLR for the .NET developer. Now I just need to find the need to concatinate rows again...
11 August 2008 - How to configure Database Mail in SQL Server 2005 to send mail
Full article - Roman Rehak wrote a great article on how to configure Database Mail in SQL Server 2005
Roman Rehak wrote a great article on how to configure Database Mail in SQL Server 2005 over on searchsqlserver.com. I needed to implement this a while back, and though it was straight forward to setup unlike SQL Mail, I found quite a bit of interesting information in the article and wanted to share it.
3 August 2008 - Calculate the Number of Week Days Between two Dates with T-SQL
Full article - How To Calculate the Number of Week Days Between two Dates - Jeff's SQL Server Blog.
This morning I came across an interesting post on calculating the number of week days between two dates. I have, or at least do not recall needing this requirement in the past but with all the reporting and task management systems that I am currently or foreseeable be working on, I have a strong feeling that this will come up sooner or later. The solution proposed on Jeff's SQL Server Blog is really quit simple, utilizing the DateDiff day and week properties, and if you have a holiday schedule you can exclude those dates as well. One think that I know I would need to ensure is making sure the start and end dates passed in are not weekends or holidays.
or
... since the DateDiff() function with weeks returns the number of week "boundaries" that are crossed; i.e., the number of weekends.
If you have a table of holidays, then you can simply subtract them out as well:
DateDiff(ww, @start, @end)*2 -
(select count(*) from holidays where holiday_date between @start and @end)
Now, what if the start day or the end day is on a weekend? In that case, you need to define what to do in those situations in your requirements.
For example, if the start date is Sunday, Nov 20th, and the end day is Monday, Nov 21st -- how many week days are between those dates? There's no universal correct answer; it could be 0, or 1, or perhaps even "undefined" (null) depending on your needs.
18 July 2008 - SQL Server Get Date Portion from DateTime Field
Full article - Date and Time Manipulation in SQL Server 2000
I needed to return just the date portion of a DateTime field from SQL Server and found a good article at sqljunkies.com (link above) that goes in to good depth of all the various date formatting codes that you can use. To simeply return the date portion in US format:
SELECT CONVERT(VARCHAR,DateColumn,101) AS DateColumn FROM Table
Were 101 is the format of the date you want to return, in this case mm/dd/yyyy. 104 will return the date in the English format (which always seems much more logical to me) dd/mm/yyyy. (for convenience I am including the full list of codes below).
|
Style ID |
Style Type |
|---|---|
| 0 or 100 | mon dd yyyy hh:miAM (or PM) |
| 101 | mm/dd/yy |
| 102 | yy.mm.dd |
| 103 | dd/mm/yy |
| 104 | dd.mm.yy |
| 105 | dd-mm-yy |
| 106 | dd mon yy |
| 107 | Mon dd, yy |
| 108 | hh:mm:ss |
| 9 or 109 | mon dd yyyy hh:mi:ss:mmmAM (or PM) |
| 110 | mm-dd-yy |
| 111 | yy/mm/dd |
| 112 | yymmdd |
| 13 or 113 | dd mon yyyy hh:mm:ss:mmm(24h) |
| 114 | hh:mi:ss:mmm(24h) |
| 20 or 120 | yyyy-mm-dd hh:mi:ss(24h) |
| 21 or 121 | yyyy-mm-dd hh:mi:ss.mmm(24h) |
| 126 | yyyy-mm-dd Thh:mm:ss.mmm(no spaces) |
| 130 | dd mon yyyy hh:mi:ss:mmmAM |
| 131 | dd/mm/yy hh:mi:ss:mmmAM |