Extracting dates from XML stored in SQL Server 2005

I have recently started storing xml files within SQL 2005. I have a problem extracting a date of format 20-02-2007 out of the XML and storing it in a field of datetime. The database is in American format (2007-02-20) and is failing when I try and insert the date. In the past when extracting the date at .Net level and passing through to SQL Server this was never a problem. I have tried to convert the date first but still fails. Is there an easy way to get around this problem or do I need to store the date as a string?

Thanks

Danny

You could use CONVERT function in you T-SQL statements with 105 style:

declare @.dt varchar(10)

set @.dt ='20-02-2007'

select convert(datetime,@.dt,105)

--RESULT

2007-02-20 00:00:00.000

|||

Yes this worked, but I had to convert to varchar before converting to datetime (cannot convert XML directly to datetime).

Thanks

Danny

Extracting data to CSV file

How do you write a query in Query Analyzer that extracts data from a table
into a .CSV file. I need the data in that format in order to upload into an
old Sybase database. ThanksEither use BCP, or select the Query / Results to File option in QA, or
use DTS, or use OSQL with the output switch option. In principle you
can do these things from a script in QA but usually that's not
necessary. If you want to automate it then use SQL Agent or some other
process to invoke one of those methods.
David Portas
SQL Server MVP
--|||SELECT ColA + ',' + ColB + ',' + ColC FROM TableName
Or
Use DTS to export the table to a CSV file
Or
Use DTS to export the file directly to Sybase.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ChrisB" wrote:

> How do you write a query in Query Analyzer that extracts data from a table
> into a .CSV file. I need the data in that format in order to upload into a
n
> old Sybase database. Thanks|||Hi
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
bcp northwind.dbo.ww out d:\test1.txt -c -t, -SMyServ -Uuser -Ppass
exec master..xp_cmdshell 'BCP northwind..ww IN
d:\test1.txt -c -C850 -SMyServ -Uuser -Ppass'
"ChrisB" <ChrisB@.discussions.microsoft.com> wrote in message
news:923AF22E-DF46-4DDB-84BE-18FCAEBE09FF@.microsoft.com...
> How do you write a query in Query Analyzer that extracts data from a table
> into a .CSV file. I need the data in that format in order to upload into
an
> old Sybase database. Thanks|||The query requires nothing special. Just execute the query within Query
Analyzer with the results going to the grid. When you get the results back,
click the mouse in the grid and choose the menu option File.. Save As..
"ChrisB" <ChrisB@.discussions.microsoft.com> wrote in message
news:923AF22E-DF46-4DDB-84BE-18FCAEBE09FF@.microsoft.com...
> How do you write a query in Query Analyzer that extracts data from a table
> into a .CSV file. I need the data in that format in order to upload into
> an
> old Sybase database. Thanks