Use the following
SELECT Doc.value('data((/OrganisationAccountOpeningForm/KYCCustomer/EntryDate)[1])','datetime')
or syntactically
SELECT <XML Column Name>.value('data((/<XPATH>)[<1-based index into possible values returned>])','<Format to convert the XML value into>')
See also http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx
Lets say we have some data in a SQL table that represents a series of events with the time that the event occured. The problem is your can't group this data because each Date is exact. You need to round it. Try this.
SELECT
cast((cast(cast (getdate() as float)*288.0 as int)+0.00001) /288.0 as datetime) as TimeToFiveMin,
cast((cast(cast (getdate() as float)*96.0 as int)+0.000003) /96.0 as datetime) as TimeToFifteenMin,
cast((cast(cast (getdate() as float)*24.0 as int)+0.00000075) /24.0 as datetime) as TimeToHour
Note that the small decimal takes care of rounding issues to millisecond accuracy for normal SQL formatting. You may need to tweak this if you use higher accuracy.
Don't forget that in order to group values you need to repeat the value in the select, i.e.
SELECT
cast((cast(cast (DateCreated as float)*288.0 as int)+0.00001) /288.0 as datetime) as TimeToFiveMin,
count(*) as count
FROM dbo.tblMailSendAttempt where DateCreated > getdate() -1
group by cast((cast(cast (DateCreated as float)*288.0 as int)+0.00001) /288.0 as datetime)
order by 1 DESC
Must be about a month ago now, myself and James were trying to work out the difference between select * from a inner join b on a.x = b.y and a.z = az
and
select * from a inner join b on a.x = b.y where a.z = az
Thing is we couldn't find a difference and thought it may even assist in the sql planning since the predicates are placed as soon as they can be which should hopefully mean sql plans the filters sooner. However it never seemed to pan out.
Today however I've found a difference, the join filter is applied before the outer join and in where the data was instead of eliminating a row, I got an outer join on nulls.
declare @a table (data varchar(30), x int)declare @b table (x int, data varchar(30) null)insert into @a values ('hello',1)insert into @b values (1,'world')select a.data, b.data from @a a left outer join @b b on a.x = b.x and b.data != 'world'select a.data, b.data from @a a left outer join @b b on a.x = b.x where b.data != 'world'
It gets better, should you apply a join hint to the query there is NO visible difference in the query plans shown
select a.data, b.data from @a a left outer hash join @b b on a.x = b.x and b.data != 'world'select a.data, b.data from @a a left outer join @b b on a.x = b.x where b.data != 'world'
While I already mentioned a link to recovering a DataBase without an easy backup, here are the previous commands I used to do it.
SELECT state_desc FROM sys.databases WHERE name = 'CommunityServerAlSki';
ALTER DATABASE CommunityServerAlSki SET EMERGENCY;
ALTER DATABASE CommunityServerAlSki SET SINGLE_USER;
use CommunityServerAlSki
DBCC CHECKDB
DBCC CHECKDB (CommunityServerAlSki, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS
ALTER DATABASE CommunityServerAlSki SET Online;
ALTER DATABASE CommunityServerAlSki SET MULTI_USER;
So my site has been down for far too long, but that's because I have.
Moved to an external host due to the failure of my server box.
Moved to BlogEngine.Net from CommunityServer
Recovered my machine to get the original posts back out
I replaced one half of mirrored pair - 250Gb SATA
added additional cooling to the hardrives
Anybody have any way to get HDD temps out of a mirrored pair please
Recovered a damaged DB
using emergency mode and DBCC REPAIR ALLOW DATA LOSS (http://www.sqlskills.com/blogs/paul/default,date,2007-10-02.aspx)
Exported the posts
Using BlogML
Now I just have to import them all
If you need to enable SQL CLR and can't get at the Surface area configuration tool.
EXEC sp_configure 'clr enabled', 1;RECONFIGURE WITH OVERRIDE;GO
Simple really. Of course you will need permissions or else you get
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Line 3
You do not have permission to run the RECONFIGURE statement.
Now they say that the only thing your can be sure of as an aspiring good developer is that the "good" code you write today will become the "bad" code of tomorrow. So with that in mind, its time for a turnaround of opinion that so big I should be applying for a government position.
Old Opinion
Unit testing and databases doesn't work. Instead you should mock your DAL.
New opinion
It may be better to use a database for unit testing when you have an easily scriptable database build.
Why?
I've recently put a lot of work into developing a mockable DAL.
I refactored all my data access into a single class
I made the class virtual
I went through each test scenario and recreated a series of calls to supply the data
This step took considerable effort in first ascertaining the order of the calls and then supplying the correct into. These are not trivial cases.
This code is disposable. It is never used in a final product. It is only for Unit testing.
I added Asserts to ensure my results were correct
Instead of doing all the work to Mock the DAL what I should do is Mock the Db. If I had a simple way of creating a Unit test database then I wouldn't need to write all the disposable code.
I've also been having problems keeping my pre-prod database and my development database in sync, so articles like Jeff's Get Your Database Under Version Control or Is your database under version control make sense.
Although its only today that the unit testing light-bulb has clicked on.
What I really need is a Mock Db. A process scripts it into life. Unit tests can truncate tables and insert just the rows that they need.
Just written my first C# SQL function, here it is in all its glory.
[Microsoft.SqlServer.Server.SqlFunction]
public static DateTime GetSunday(DateTime original)
{
DateTime result = new DateTime(original.Year, original.Month,
original.Day - ((int) original.DayOfWeek),
0, 0, 0);
return result;
}
Then I went and found out how to really go to town. http://msdn.microsoft.com/.../sqlclrguidance.asp, so here is my 2nd function (and helper)
[Microsoft.SqlServer.Server.SqlFunction
(FillRowMethodName="FillGetAllDates",
TableDefinition="Date datetime")]
public static IEnumerable GetAllDates(DateTime start, DateTime end)
{
DateTime date = start.Date;
List<DateTime> result = new List<DateTime>();
while (date <= end)
{
result.Add(date);
date = date.AddDays(1);
};
return result;
}
public static void FillGetAllDates(object row, out DateTime date)
{
date = (DateTime) row;
}