How To Convert UTC Based Datetime Values To Local Setting?

Sep 11, 2007

Hello:

I've recently discovered the MS CRM 3.0 stamps any datetime field in SQL as UTC (aka GMT). Even though the end user may select 12:00 noon on the CRM gui interface, the datetime is stamped in SQL as 16:00 (I'm in EST).

So, is there an easy way to read the 16:00 back to local EST, taking into account daylightsavings (DST)? Apparently in VB or C# this conversion is a trivial matter of using ToLocalTime(), but I don't believe SQL 2005 has such a convenient function.

I'm currently building a SQL view that I wish to use to support multiple reports, and it would be ideal for the view to translate the UTC datetime fields (even if via a user-defined function) to EST rather than coding every report or custom app to translate the UTC.

Any suggestions or links to code will be greatly appreciated!

Thanks,
Pete

View 5 Replies


ADVERTISEMENT

Converting UTC Datetime Values To Local Time Zones

May 20, 2008



I would like to convert a UTC datetime value to any Local Time Zone. Below is some code I'm trying to ensure works correctly. Anyone care to comment?

I have used the following lines before the function to obtain the 2nd parameter:


SELECT GETUTCDATE()

SELECT GETDATE()

SELECT DATEDIFF(hh,'2008-05-20 20:08:01.020', '2008-05-20 16:08:01.020')

DECLARE @UTC DATETIME

EXECUTE @UTC = UTCtoLocalDate '2008-05-20 20:08:01.020', -4


-- =============================================

-- Description: <Converts local datetime values to UTC datetime values, using the built-in GETUTCDATE() function.

-- <Parameter two (@TZ) represents the time zone difference from UTC/GMT. To obtain this value

-- <run GETUTCDATE() and GETDATE(), then determine the timezone from the difference between the two.>

-- =============================================

CREATE FUNCTION [dbo].[UTCtoLocalDate] (@UTCDate DATETIME, @TZ INT)

RETURNS DATETIME AS

BEGIN

IF ( DATEPART(hh, @UTCDate) <> 0 )

BEGIN

DECLARE @LocalDate DATETIME

DECLARE @UTCDelta INT

DECLARE @thisYear INT

DECLARE @DSTDay INT

DECLARE @NormalDay INT

DECLARE @DSTDate DATETIME

DECLARE @NormalDate DATETIME

SET @thisYear = YEAR(@UTCDate)

IF (@thisYear < 2007 )

BEGIN

SET @DSTDay = ( 2 + 6 * @thisYear - FLOOR(@thisYear / 4) ) % 7 + 1

SET @NormalDay = ( 31 - ( FLOOR( @thisYear * 5 / 4) + 1) % 7)

SET @DSTDate = '4/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

SET @NormalDate = '10/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

END

ELSE

BEGIN

SET @DSTDay = ( 14 - ( FLOOR( 1 + @thisYear * 5 / 4 ) ) % 7 )

SET @NormalDay = ( 7 - ( FLOOR ( 1 + @thisYear * 5 / 4) ) % 7 )

SET @DSTDate = '3/' + CAST(@DSTDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

SET @NormalDate = '11/' + CAST(@NormalDay AS VARCHAR(2)) + '/' + CAST(@thisYear AS VARCHAR(4)) + ' 2:00:00.000 AM'

END

IF ((@UTCDate > @DSTDate) AND (@UTCDate < @NormalDate))

BEGIN

SET @UTCDelta = @TZ + 1

END

ELSE

BEGIN

SET @UTCDelta = @TZ

END

-- now convert utc date to local date

SET @LocalDate = DATEADD(Hour, @UTCDelta, @UTCDate)

END

ELSE

BEGIN

SET @LocalDate = @UTCDate

END

RETURN(@LocalDate)

END

GO

View 6 Replies View Related

How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone

Aug 7, 2007



Hi all,

I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.

I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.

For example if the date is August 07, 2007 10:00 AM UTC,

then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.


Similarly for other Time Zones it should display the time accordingly.

Is this possible in SSRS 2005?

Any pointers will be usefull...

Thanks in advance
sudheer racha.

View 5 Replies View Related

Convert Datetime String To Datetime Date Type

Mar 11, 2014

I am inserting date and time data into a SQL Server 2012 Express table from an application. The application is providing the date and time as a string data type. Is there a TSQL way to convert the date and time string to an SQL datetime date type? I want to do the conversion, because SQL displays an error due to the

My date and time string from the application looks like : 3/11/2014 12:57:57 PM

View 1 Replies View Related

How To Convert Datetime From Text/char To Datetime

Jul 20, 2005

Hi,I have a text file that contains a date column. The text file will beimported to database in SQL 2000 server. After to be imported, I wantto convert the date column to date type.For ex. the text file look likeName dateSmith 20003112Jennifer 19991506It would be converted date column to ydm database in SQL 2000 server.In the table it should look like thisName DateSmith 2000.31.12Jennifer 1999.15.06Thanks in advance- Loi -

View 1 Replies View Related

Convert DateTime To A DateTime With Milliseconds Format

Nov 5, 2007

Hi,

I am trying to access a date column up to millisecond precession. So I cast date to as follows:



Code BlockCONVERT(varchar(23),CREATE_DATE,121)


I get millisecond part as a result of query but it€™s €œ000€?.

When I try to test the format by using getDate instead of DateTime column I get right milliseconds.





CONVERT(varchar(23),GetDate(),121) --Gives right milliseconds in return

View 4 Replies View Related

Millisecond Values Missing When Inserting Datetime Into Datetime Column Of Sql Server

Jul 9, 2007

Hi,
I'm inserting a datetime values into sql server 2000 from c#

SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime

C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?

thanks,
Mani

View 3 Replies View Related

How To Convert Datetime From Varchar To Datetime

Sep 11, 2007

hi,
How do i convert a varchar field into the datetime data type? the reason i need this lies in the requirement that in the earlier data base the column that is hlding the date value is having the data type as varchar. and in the new design the column data type is datetime. i am using sql scripts for the data migration from the older design to the newer and got stuck with this datetime convertion issue. do let me know the best possible solution.

following are the sample data that is theer in the older table for the date.


12/12/2003
1/13/2007
01132004
1-1-2004
1.2.2001



there is no uniformity of the data that is stored currently.



thnkx in adv.
rahul jha

View 11 Replies View Related

Setting Built-in Account To Local System

May 23, 2008

Hi folks.

When installing SQLServer 2005 Express, I use the following command line:

cmdline = " /qb ADDLOCAL=SQL_Engine,SQL_Data_Files SECURITYMODE=SQL INSTANCENAME=MyServer SAPWD=MyPwd DISABLENETWORKPROTOCOLS=0 SQLAUTOSTART=1 requiresmsiengine=1"

I noticed that when installing on a Windows XP machine, the installation results in a SQLServer instance which is configured with Built-in account = Network Service. However, with the same command line used on Windows 2000 machines, the configuration winds up being Built-in account = Local System. My understanding is that the default configuration is supposed to be Local System.

What can I do to ensure that the instance configuration is always Local System during the silent installation? This is required otherwise, under the Network Service configuration, it creates a messy situation to attach DBs.

Thanks!

Mike

View 1 Replies View Related

Setting Up Remote Connection To Server From Local Machine

Sep 17, 2015

I have a SQL Server 2014 installation on a server (CHRIS-PCCHRISSQL).I have SQL Server 2014 management studio installed on local server called Pootle.I have gone through the configuration on server (CHRIS-PCCHRISSQL) inc the following:

(1) I have set up a user on CHRIS-PCCHRISSQL called sqladminuser at server level with the Server Roles of 'Public' and 'SysAdmin'

(2) The computers are both on the same homegroup.

(3) On Chris-PC , I have opened up the firewall port 1433 as Inbound Rule

(4) On Chris-PC, Within SQL Server Configuration Manager,the 'SQL Server Network Configuration for Protocols for CHRISSQL' have been set up as follows:

- The TCP protocol is enabled
- I have set up IP2
as follows:

Active: Yes
Enabled: No
IP Addres: 192.168.0.3

However when I try to connect from SQL Server Management Studio 2014 on my local machine Pootle to Chris-PCCHRISSQL using SQL Server Authentication with the user sqladminuser

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)how to set up a remote connection from SQL Server Management Studio
on my local machine Pootle to CHRIS-PCCHRISSQL

View 6 Replies View Related

Transact SQL :: Convert Comma Separated String Values Into Integer Values

Jul 28, 2015

I have a string variable

string str1="1,2,3,4,5";

I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :

declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)

But this query throws following error message:

Conversion failed when converting the varchar value '3,4,6,7' to data type int.

View 4 Replies View Related

T-SQL (SS2K8) :: Count Number Of Values That Exist In A Row Based On Values From Array Of Numbers

Apr 16, 2014

How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:

Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10

------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)

Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10

Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5

View 2 Replies View Related

Power Pivot :: Calculating Values For Future Dates Based On Past Values

Nov 13, 2015

I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?

View 6 Replies View Related

T-SQL (SS2K8) :: Dynamically Change Values In Script Based On 3 Values

Feb 27, 2014

I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the

1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:

where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)

2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.

3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)

If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.

Again, the series are usually discontinuous and specific to each specialty.

Here's the portion of the script (it's at the end) that I'm talking about:

--change values in square brackets below for each specialty as needed and change the denom number in the very last query.

if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T

[Code] ....

If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.

View 6 Replies View Related

Null Values For Datetime Values Converted To '0001-01-01'

Mar 29, 2006

Hi

can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.




If Not Row.Datum1_IsNull Then

Row.OutputDatum1 = Row.Datum1

Else

Row.OutputDatum1 = CDate(System.Convert.DBNull)

End If



Any help welcome.

View 1 Replies View Related

Convert UTC To Local Time

Aug 1, 2014

I have a stored procedure, it is accepting a parameter which is in local datetime format. But the front end application is passing UTC date format. How I can convert UTC date time to local date time...

View 2 Replies View Related

Rsconfig Command Format For Local Named Instance - Setting Up An Unattended Account

Dec 27, 2007

my local instance of reporting services is named and therefore I think causing me a problem when I issue the following command to set up an unattended account...

rsconfig -s localhostinstance name -e -u domain nameuser name -p password


the message I keep getting is "No Reporting Services instance found on local host.". I tried a couple of things including replacement of the word localhost with my computer name but to no avail. I tried single and double quotes around the -s parameter but no success.

Anybody know how to do this?

View 2 Replies View Related

Setting Datetime Variable W/ Mm/dd/yyyy

Jul 19, 2007

I am having the wrost trouble with this today for some dumb reason...
Please don't suggest any alternates; this is just a quick example, full code is more elaberate.

Today is 07/19/2007
Declare @StartDate DateTime
@StartDate = CONVERT(VARCHAR(10), Month(GetDate()) & "/22/" & Year(GetDate()), 101)

So I want @StartDate = '07/22/2007'

What I need is CDate

View 6 Replies View Related

Transact SQL :: Convert Certain Row Values To Next Column Values

Jul 9, 2015

I have a table with 2 columns and my source data looks like this..

PolicyNum   InsCode    

1ABC12          1001        
1ABC12          1002        
1ABC12          1003       
1ABC12          1004       
1ABC12          1005        

[Code] ....

My output should look like this..I need T-sql to get below output.

PolicyNum   InsCode1   InsCode2    

1ABC12             1001       1005       
1ABC12             1002       1006        
1ABC12             1003       1004       
1ABC20             1001       1005        

[Code] ...

Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.

Rule-1: InsCode value 1001 should always mapped to 1005   
            InsCode value 1002 should always mapped to 1006
            InsCode value 1003 should always mapped to 1004 

Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1

In the above sample data..

for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing

Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')        

Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')       
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')      

[Code] ....

View 14 Replies View Related

Select Values From One Table Based Upon Values In Another...

May 19, 2006

How do I:Select f1, f2, f3, from tb1 where f1=Select f1 from tb2 where f1='condition'?

View 3 Replies View Related

DateTime Format Problem: Setting To 16/Mar/2006 8:50:00 AM

Aug 2, 2006

my Current DateTime Format is         01/08/2006 9:15:00 AM
i want to set it to                                01/Aug/2006 9:15:00 AM
what parameter will be pass in "SET DATEFORMAT"?
plz help and give me a chance of thanks.
 

View 7 Replies View Related

Need Help Setting Default DateTime On SqlDataSource Control

Jan 21, 2007

I thought this would be easy.  I have a repeater control and a sqldatasource control.  I am trying to filter the select statement using DateTime.Now.ToString() and keep getting an invalid date string format.  The control is on a content page in my asp.net site.  On the master page this <%= DateTime.Now.ToLongDateString() %>  works to display the current date.  If I try and put <%= DateTime.Now.ToString() %> in the Default value of the SelectParameter it does not work.  No intellisense either so I am assuming I am missing something.  Here is the code... pretty basic really.
<asp:Repeater ID="Repeater1" runat="server" DataSourceID="sqlDSnews">
<ItemTemplate>
<h3><%# Eval("newTitle")%></h3>
</ItemTemplate>
</asp:Repeater>
<asp:SqlDataSource
ID="sqlDSnews"
runat="server"
ConnectionString="<%$ ConnectionStrings:XXXX%>"
SelectCommand="SELECT [newTitle], [newsDetails], [dateExpires], [newsImage], [dateCreated] FROM [News] WHERE (([GroupID] = @GroupID) AND ([dateExpires] >= @dateExpires))">
<SelectParameters>
<asp:QueryStringParameter DefaultValue="0" Name="GroupID" QueryStringField="Gid" Type="Int32" />
<asp:Parameter Name="dateExpires" DefaultValue='<%= DateTime.Now.ToString() %> 'Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>
** NOTE the DateTime does not show up in blue - if that helps with a solution **

View 5 Replies View Related

Setting Parameter Value Based On Matrix Column

Apr 10, 2007

Hello all.

I am on the verge of being able to do exactly what I want, but just can't seem to find the right combination of things to do it. I'm sure all of you wonderful folks will be able to point it out to me immediately, but I've been looking at it too long or something....


I have a record of individual sales with the state, and quarter of the sale.

sale_id state quarter
001 NY 2005Q1
003 WI 2006Q2
etc.


I create a report with a matrix to show count(sale_id) with Quarter as the column group and State as the row group. This works fine.

Now what I want to do is to get percentages based on quarterly sales. In other words, what percent of sales for 2005Q1 in NY vs. all sales in 2005Q1. So I create a second dataset (called total) with an SQL query like so:

SELECT count(sale_id)
FROM data_table
WHERE quarter = @QueryQuarter

Now, back in the matrix I want to use the column that we're in (2005Q1, 2005Q2, etc.) as the value that is passed to this query.

This is a simple concept, but I can't seem to figure out the correct call to pass the column group to the query as the parameter.

Thank you for any pointers you might be able to give. As I said, I'm right on the verge and just can't quite get it.

cmk

View 3 Replies View Related

Power Pivot :: How To Convert Date / Time From UTC To Local (CET) Considering DST

Oct 27, 2015

I am importing data from Dynamics CRM online using ODATA in Power Query. All datetimes in the database is stored in UTC and I need to convert these into CET. But it's not as simple as just to add 1 hour to the datetime from the database because due to Daylight. Savings time on half the dates I need to add 2 hours - how can I do this?

View 3 Replies View Related

Transact SQL :: Convert Local Date Time Field To GMT?

Jul 28, 2015

I need to convert Local date time field to GMT. [Does we need to consider daylight saving and so on?]

[URL]

View 5 Replies View Related

Server Error 1431 When Setting Up Database Mirroring Using Local System Accounts And Certificate Authentication

May 24, 2006

I attempted to setup database mirroring using a High Availability scenario but when I installed SQL is chose to use local system accounts for all the services. Consequently, I stubled upon a microsoft article explaining how to setup mirroring using local system accounts and certificate authentication but I am stil not able to get it to work. When I try ti initiate the mirror from the mirror server I receive an error stating "Neither the partner nor the witness server instance for database "EDENLive" is available. Reissue the command when at least one of the instances becomes available." I have checked all the endpoints and everything seems to be in order. I even checked to make sure that each server was listening on the appropriate ports and I AM able to telnet to the ports. Please help!

View 1 Replies View Related

Setting Select Parameter Based On Config File

Jun 17, 2008

Hi everybody,
Is there a way to set SelectParameter for SQLDataSource in ASPX file using System.Configuration.ConfigurationManager.AppSettings["SiteID"]) ?
Thanks a lot in advance. 

View 8 Replies View Related

Convert To DateTime

Dec 26, 2006

I have a date filed 12/26/2006 and a time field 7:00am. How can I combine them in my select statement and get a DateTime field.

View 3 Replies View Related

Convert Datetime.

Mar 29, 2004

Hello All,

In following statement in SQL, I'm first converting 'IssueDate' with style 101 into 'nvarchar' then converting to Datatime.

convert(datetime,convert (nvarchar,Cert_WarehouseDetails.IssuedDateX,101)) <= '3/29/2004')


Which is right in below one.

1. Do I need to first convert into nvarchar then datetime.
e.g. convert(datetime,convert (nvarchar,Cert_WarehouseDetails.IssuedDateX,101)) <= '3/29/2004')


2. Otherwise can I directly convert into datetime.

convert(datetime,Cert_WarehouseDetails.IssuedDateX ,101) <= '3/29/2004')


Please reply to me asap.


Regards,
M. G.

View 3 Replies View Related

Convert Datetime To MM/DD/YY HH:MM AM/PM

Apr 7, 2008

Hi,
Is there a way to convert the date time column to MM/DD/YY HH:MM AM/PM format.
I tried
Select Convert(varchar , Getdate(),100). But this is not in MM-DD-YY format..

Thanks in advance..

View 4 Replies View Related

Convert Datetime.

Jul 11, 2006

Hi all..its kinda hard for me hw to figure out this, hopefully any of u guys can help me out with this super simple problem..

here is my query..
select convert(char(50),dateadd(day,-7,getdate()),105)
its because i want it to look last week data. BUT i get the format like this '03-07-2006'

what i want is it to be like this '20060703' how do i do that?

Thanks,
Jack

View 3 Replies View Related

Convert Datetime

Aug 29, 2007

I'm having trouble converting a date of birth field, datatype int from yyyymmdd to mmddyyyy.

select 'DOB' = Convert(char(10), pat_dob, 101) from patfile

what am I missing?

View 4 Replies View Related

Convert Int To Datetime

Dec 14, 2007

I have a table with an int field that I'm trying to
insert into a datetime field, however, there are 0's in the int field. How do I write a case statement to change the 0's to '01/01/1900' and then store the datetime field as 'mm/dd/yyyy'? The data is currently coming in as yyyymmdd as int.

Sample Data:
19720518
19720523
19720523
19720601
19720603
19720609

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved