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

Converting Access Reports to SRS 2005

Hello Everyone,
Is there a wizard of Visual Studio add-in that I can use to convert MS Access
Reports to SRS 2005?
Regards,
Pete Zerger, MCSE(Messaging) | MCTS(SQL 2005) | MVP - MOM
Founder, SystemCenterForum.org
URL:http://www.systemcenterforum.org
BLOG: http://www.it-jedi.net/
mailto:pete.zerger AT gmail.coOn Apr 21, 5:51 pm, Pete Zerger <pete.zer...@.gmail.com> wrote:
> Hello Everyone,
> Is there a wizard of Visual Studio add-in that I can use to convert MS Access
> Reports to SRS 2005?
> Regards,
> Pete Zerger, MCSE(Messaging) | MCTS(SQL 2005) | MVP - MOM
> Founder, SystemCenterForum.org
> URL:http://www.systemcenterforum.org
> BLOG:http://www.it-jedi.net/
> mailto:pete.zerger AT gmail.com
I use Visual Studio 2005 Professional, and the SSRS Report Designer
has a import utility for Access Report definitions built right into
it. If you start a Report Project, and then right-click on the
Reports folder within the Solution Explorer window, you should see an
option to "Import Reports, Microsoft Access..." appear in a context
menu.
I don't know if BIDS or VB.NET has the same options - I would guess
they do, however.

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.

Convert Milliseconds to HH:MM:SS

I am writing a report that Queries a SQL DB using 'SQL Server Business Intelligence Development Studio'. I have a field in the DB called duration and it is in milliseconds. I am trying to find an easy way to convert the format from Milliseconds to HH:MM:SS.

Nearest I can get is the following for the field:

=Int(((Fields!DURATION.Value/1000) / 60) / 60) & ":" & Int(((((Fields!DURATION.Value/1000) / 60) / 60) - Int(((Fields!DURATION.Value/1000) / 60) / 60)) * 60)

The output is in HH:MM. One issue with this is if the MM is say :03, it prints as :3. I lose the leading 0 so 9:03 (9hrs and 3 minutes) prints as 9:3. Where as 9:30 (9 hrs and 30 minutes) prints as 9:30 as it should.

Is there an easier way to do this?

TIA...

Mike...

Try using the following expression, which uses the TimeSpan struct.

=TimeSpan.FromMilliseconds(Fields!DURATION.Value).ToString()

Ian|||

This is the code I use, using a custom code function. hope it will help

J

Shared Function FormatTime(ByVal Seconds As Integer) As String
Dim str As String
Dim hour As Integer
Dim min As Integer
str = ""
hour = Abs(Seconds\3600)
min = Abs((Seconds MOD 3600)/60)
If Seconds>= 0
str = iif(hour <= 9, "0" & CStr(hour), FormatNumber(hour, 0, , ,TriState.True)) & ":" & iif(min <= 9, "0" & CStr(min), CStr(min))
Else
str = "-" & iif(hour <= 9, "0" & CStr(hour), FormatNumber(hour, 0, , ,TriState.True)) & ":" & iif(min <= 9, "0" & CStr(min), CStr(min))
End If
return str
End Function

use like this

=Code.FormatTime( Fields!yourfeildname.Value)

|||

this worked good except the seconds come out as a long decimal (09:11:04.982345). How do I round that off. doesn't have to be exact, just need to get rid of the stuff to the right of the decimal.

thx....

|||

You should just be able to format the output, or use the format command to convert the displayed output to whatever you require ( see BOL )

J

|||Try using FromSeconds and convert the milliseconds to seconds whole seconds. Examples,

Without Rounding:
=TimeSpan.FromMilliseconds(Int(Fields!DURATION.Value/1000)).ToString()

or

With Rounding:
=TimeSpan.FromMilliseconds(CInt(Fields!DURATION.Value/1000)).ToString()

Ian

Convert from Access DB to SQL DB....

Dear All,

I would like to convert from Access To SQL DB undervisual Studio.Net 2005...

How can I do it easily, or if there any software to do this automaticlly, please your help..

Awaiting your valuable reply.

Many thanks in advance for your cooperation and continuous support...

What version of MS Access are you using?

Most of the Access versions like XP/2003 all have an upgrade wizard that allows you to convert your existing database to SQL Server.

In Access 2003 I believe you can use the Upsizing Wizard which can be accessed from Tools -> Database Utilities -> Upsizing wizard

Alternatively you can always import all the tables/data/procedures into SQL Server using Enterprise Manager/Management Studio.

|||

Microsoft guide you to good support for this:

The easiest way to convert an Access database to SQL Server is to use the Upsizing Wizard. The Upsizing Wizard:

?Preserves database structure, including data, indexes, anddefault settings.?Automatically converts Access validation rules and defaultsettings to the appropriate SQL Server equivalents.?Maintains table relationships and referential integrityafter you upsize.

To run the Upsizing Wizard in Access 2000, on theTools menu, point toDatabase Utilities, and then clickUpsizing Wizard.

You will got details information from his link

http://support.microsoft.com/kb/307598

Ahsan

|||

Thanks for your replying...

I've got Access 2007, and I've done this way.

However, it asks me to choose the Sql server and it gives me connection error when I use local !!!, and I'm using the Visual Studio.Net 2005 which has Sql Express Edition..

So, I would like to migrate the Access DB to my project in Visual Studio 2005 ( ASP.net -> C# language ).

I'd like you to know also that I haven't installed SQL Server 2005 or 2003. Do u think that I need it to do this?

Many thanks...

|||

ali Manshow:

However, it asks me to choose the Sql server and it gives me connection error when I use local !!!, and I'm using the Visual Studio.Net 2005 which has Sql Express Edition..

what is your SQLEXPRESS instance called as this should be the name you should be typing into access

ali Manshow:

I'd like you to know also that I haven't installed SQL Server 2005 or 2003. Do u think that I need it to do this?

SQLExpress is enough and you would not need to install SQL Server 2000/2005

|||

You should not install the SQL server 2005 because visual studio 2005 has built in Sql Server 2005 Express. So you can easily use this.For connect with sql express you simply use ./SQLExpress in server field.

Ahsan

|||

Thanks I have tried this, but it still gives me connection error because of wrong Sql Server or Access Denied..??

Do u think that I have to change the settings for SQL login:

There are 3 options:

1) Local Service.

2) Local System.

3) Network Service.

Which options shall I choose.

Or do I need to change any settings in Access 2007 DB.

Thanks so much...