converting datetime formats and layouts?

Opening DimTime table of AdventureWorksDW sample database in MS SQL Server Management Studio shows me values in FullDateAlternatKey like

01.07.2001 0:00:00

select fulldatealternatekey from dimtime
gives me the results like
2001-07-01 00:00:00.000

(1) Why is it?
(2)How can I "SELECT fulldatealternatekey" in different format like 9/3/2001 0:00
(that is given in SampleCurrencyData.txt ?
Well, I am trying to follow Integration Service Tutorial, Lesson1 (SQL Server 2005 Books Online) and have yet another format of datetime like 9/3/2001 0:00 in SampleCurrencyData.txt and receive the type mismatch in part "To add and configure the DateKey Lookup transformation" (8.In the Available Input Columns panel, drag CurrencyDate to the Available Lookup Columns panel and drop it on FullDateAlternateKey.)

Flat File Connection Manager Editor --> Advanced --> DataType gives me following formats:
- file_timestamp [DT_FILETIME]
- database date [DT_DBDATE]
- database time [DT_DBTIME]
- database timestamp [DT_DBTIMESTAMP]
- date [DT_DATE]
- file timestamp [DT_FILETIME]

(3)
BOL2005 give me general description of these date-times but how do I govern separation delimitoers in them (., /, -)?

That is the recommended way to store dates in SQL Server. The best way to represent dates in your application is to extract them as-is from the database, and format them using the application language's built-in string formatting functions. Here is a list of them for C# -http://blog.stevex.net/index.php/string-formatting-in-csharp/|||

Really, my problem has nothing to do with C# or storing dates by ME (?!) since I am trying to reproduce SSIS tutorial (as I mentioned it more specifically above).

I found some related posts to my problem:
http://forums.asp.net/thread/1688990.aspx
converting datetime formats and layouts?

ETL Package Problem
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603488&SiteID=1

Though neither of tholutions helped me!

|||I see that I cited this same post.
Instead, one more post that describes the same problem bnut had not helped me is

collation or local sensitive settings or other configuration properties
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=180740&SiteID=1

Convert/Export to dBASE (.dbf)

Dear all,

I'm a complete beginner when it comes to MS SQL Server Management Studio Express 2005, having only downloaded it yesterday. I have a large dataset which is too big to fit into Excel or Access, but appears to fully displayn in Server. I need to convert or export this to a .dbf (dBASE IV) file but have no idea how to go about doing this. I've had a look around online, and see frequent references to FoxPro and BCP (I'm guessing that this is some kind of command line... I don't see how to open this though). I'm hoping to ultimately import the file into ArcGIS, so the output file would have to be compatible with this.

I'd be very grateful for any offer of help or suggestions, but please give me a step-by-step, dummies guide, clearly and fully explaining what you mean, as like I said, this one is new to me!

Thanks very much for your help

smurray444

BCP is the Bulk copy application which is available with the client toosl of SQL Server. The usage is pretty easy and well described in the BOL (Books Online, the help of SQL Server). You will have to provide a table name to bulk the data out, a servername, a password, a destination file and a format for the output (like comma delimited, Tab delimited etc.)

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Wow, DBase IV. That came out about 1987 or so, didn't it?

As far as I am aware, there is not a specific method to export or output a *.dbf formatted file from SQL Server (or BCP).

My first recommendation is to contact the vendor for ArcGIS, and request their assistance. I would hope that they have subtantial experience importing data from disparate sources into their product.

Otherwise, you may Google? "dbf file format" to determine how to create your own dbf file -IF you have to go that route. (I would exhaust the ArcGIS possibilities first.

convert SQL 2005 express .mdf to SQL 2005

Hi Forum, I need some good info regards remote host requirements.

I dev web app using VS 2005 express edition and SQL Server Management Studio express. Everything is ready to transfer to host running SQL 2005.

My ? do I have to convert my SQL 2005 express .mdf to a SQL 2005 file type. (hosting company state I do, then save as .bak)

much appreciate good data, Paul

A mdb file is Access based, an mdf file is the data file of SQL Server. They are not convertible. You will have to import the data from access or export / upsize from access to make them stored on the server.

|||

check out this link

http://aspalliance.com/888

|||

There are two easy solutions:

1) Attach you .mdf file to your database with the hosting company.

2) Make a backup for your database (e.g. on the local machine) and restore it in your database with the hosting company.

Good luck.

|||

CS4Ever:

There are two easy solutions:

1) Attach you .mdf file to your database with the hosting company.

2) Make a backup for your database (e.g. on the local machine) and restore it in your database with the hosting company.

Good luck.

There are two more solutions but some time it does not work due to security issues from the hosting company.

1) Use SSIS (SQL Server Integration Services) a wizard that will copy your data and objects from local machine to the hosting server.

2) Use Database Copy Wizard.

Good luck.

|||Thanks everyone for info, I ended up creating a blank db on host server and configing through SQL server manager, cheers P|||

What do you mean by:

plkilroy:

configing through SQL server manager

Please mark Answer the post(s) helped you, so it will help other to get the soltion.

Thanks.