Converted data types

I'm using SQL Servers Import function to import an entire MS Access
database.
Date/time types from Access are defaulting to smalldatetime in SQL
Server, but the actual data is too large and I get an error, or rather
many errors since this happens in many tables.
I can edit the SQL to make these datetime types but, since there are
so many, is there any way to globally change the default data mapping?
What do you mean "the actual data is too large"? What "error" do you get?
Do you really have a lot of dates that fall outside the range supported by
smalldatetime?
Anyway, there is an easy way to generate a script to alter all smalldatetime
columns => datetime.
SELECT 'ALTER TABLE ['+TABLE_SCHEMA+'].[' + TABLE_NAME + '] ALTER COLUMN ['
+ COLUMN_NAME + '] DATETIME'
+ CASE WHEN IS_NULLABLE = 'NO' THEN
' NOT NULL ' ELSE '' END
+ CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN
' DEFAULT ' + COLUMN_DEFAULT ELSE '' END + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'smalldatetime';
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"blindsey" <blindsey@.dsicdi.com> wrote in message
news:1183407383.413284.218870@.n2g2000hse.googlegro ups.com...
> I'm using SQL Servers Import function to import an entire MS Access
> database.
> Date/time types from Access are defaulting to smalldatetime in SQL
> Server, but the actual data is too large and I get an error, or rather
> many errors since this happens in many tables.
> I can edit the SQL to make these datetime types but, since there are
> so many, is there any way to globally change the default data mapping?
>

Converted data types

I'm using SQL Servers Import function to import an entire MS Access
database.
Date/time types from Access are defaulting to smalldatetime in SQL
Server, but the actual data is too large and I get an error, or rather
many errors since this happens in many tables.
I can edit the SQL to make these datetime types but, since there are
so many, is there any way to globally change the default data mapping?What do you mean "the actual data is too large"? What "error" do you get?
Do you really have a lot of dates that fall outside the range supported by
smalldatetime?
Anyway, there is an easy way to generate a script to alter all smalldatetime
columns => datetime.
SELECT 'ALTER TABLE ['+TABLE_SCHEMA+'].[' + TABLE_NAME + '] ALTER COLUMN ['
+ COLUMN_NAME + '] DATETIME'
+ CASE WHEN IS_NULLABLE = 'NO' THEN
' NOT NULL ' ELSE '' END
+ CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN
' DEFAULT ' + COLUMN_DEFAULT ELSE '' END + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'smalldatetime';
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"blindsey" <blindsey@.dsicdi.com> wrote in message
news:1183407383.413284.218870@.n2g2000hse.googlegroups.com...
> I'm using SQL Servers Import function to import an entire MS Access
> database.
> Date/time types from Access are defaulting to smalldatetime in SQL
> Server, but the actual data is too large and I get an error, or rather
> many errors since this happens in many tables.
> I can edit the SQL to make these datetime types but, since there are
> so many, is there any way to globally change the default data mapping?
>

Converted data types

I'm using SQL Servers Import function to import an entire MS Access
database.
Date/time types from Access are defaulting to smalldatetime in SQL
Server, but the actual data is too large and I get an error, or rather
many errors since this happens in many tables.
I can edit the SQL to make these datetime types but, since there are
so many, is there any way to globally change the default data mapping?What do you mean "the actual data is too large"? What "error" do you get?
Do you really have a lot of dates that fall outside the range supported by
smalldatetime?
Anyway, there is an easy way to generate a script to alter all smalldatetime
columns => datetime.
SELECT 'ALTER TABLE ['+TABLE_SCHEMA+'].[' + TABLE_NAME + '] ALTER CO
LUMN ['
+ COLUMN_NAME + '] DATETIME'
+ CASE WHEN IS_NULLABLE = 'NO' THEN
' NOT NULL ' ELSE '' END
+ CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN
' DEFAULT ' + COLUMN_DEFAULT ELSE '' END + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'smalldatetime';
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"blindsey" <blindsey@.dsicdi.com> wrote in message
news:1183407383.413284.218870@.n2g2000hse.googlegroups.com...
> I'm using SQL Servers Import function to import an entire MS Access
> database.
> Date/time types from Access are defaulting to smalldatetime in SQL
> Server, but the actual data is too large and I get an error, or rather
> many errors since this happens in many tables.
> I can edit the SQL to make these datetime types but, since there are
> so many, is there any way to globally change the default data mapping?
>

Convert UTC time to local time

Hello,

I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.

Thanks for your help.

You need to use System.TimeZone.ToLocalTime(UTCTime).

See http://msdn2.microsoft.com/en-us/library/system.timezone.tolocaltime.aspx.

You should be able to use TimeZone.CurrentTimeZone if you want to convert using the server time zone.

|||

Hello,

i tried this tip with no luck.

I used the expression = System.TimeZone.ToLocalTime(!Fields.DateTime.Value) in one of my cells and got an BC30469 error.

|||

Try this instead:
=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!DateTime.Value)

-- Robert

|||

Robert,

thanks alot. Works like a charm.

|||

This works for conversion based on the time zone of the report server, but not the client. Is that correct? I tested this by using the function in a textbox on the report that I deployed to the report server. Then on my workstation PC, I changed my timezone and viewed the report. The time in the report still reflected the time on the report server (converted from the UTC time of course).

How do you change the dates in the reports dynamically based on the area of the country someone opens the report? Because the report renders as HTML first before being delivered to the client, does this mean it will always use the report server time zone?

Thanks

-Kory

|||I have the same question as KoryS. Anyone have an answer?

Thanks.

Convert UTC time to local time

Hello,

I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.

Thanks for your help.

You need to use System.TimeZone.ToLocalTime(UTCTime).

See http://msdn2.microsoft.com/en-us/library/system.timezone.tolocaltime.aspx.

You should be able to use TimeZone.CurrentTimeZone if you want to convert using the server time zone.

|||

Hello,

i tried this tip with no luck.

I used the expression = System.TimeZone.ToLocalTime(!Fields.DateTime.Value) in one of my cells and got an BC30469 error.

|||

Try this instead:
=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!DateTime.Value)

-- Robert

|||

Robert,

thanks alot. Works like a charm.

|||

This works for conversion based on the time zone of the report server, but not the client. Is that correct? I tested this by using the function in a textbox on the report that I deployed to the report server. Then on my workstation PC, I changed my timezone and viewed the report. The time in the report still reflected the time on the report server (converted from the UTC time of course).

How do you change the dates in the reports dynamically based on the area of the country someone opens the report? Because the report renders as HTML first before being delivered to the client, does this mean it will always use the report server time zone?

Thanks

-Kory