Convert XML Date in T-SQL

Our persistent objects use an internal dataset to hold their data. When I
save to SQL Server 2000, I do so in one call to the database sending the XML
representation output from the DataSet.GetXml() method and shredding the XML
values into their tables & columns via T-SQL. Everything works great, except
for converting the default XML DateTime output from the DataSet. The output
is like this: 2004-10-20T16:00:00.0000000-05:00. But the list of compatible
T-SQL formats accepted by CONVERT does not show this format (List found here
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp).
The closest format I can see is Style 126: yyyy-mm-dd Thh:mm:ss.mmm. If I
pass the default DataSet XML date format to CONVERT it throws an error. What
I’m currently doing is pre-processing the XML string before sending it to the
database. I iterate through each known date field and convert the value
before sending to SQL Server. This seems unnecessary, but it also seems
unnecessary that the default ADO XML date output is by all appearances
incompatible with SQL Server’s T-SQL CONVERT function. What am I missing
here? Know any ideas?
Hello, MPalmer78!
You wrote on Thu, 21 Oct 2004 09:24:23 -0700:
[Sorry, skipped]
See KB811767.
With best regards, Alex Shirshov.

Convert varchar to datetime!

Hi:

I have a column call Date_Sent (28/02/2004)(dd/mm/yyyy) format as varchar at beginning. I want to convert to other column as datetime.

I use Query like:

SELECT

CAST(SUBSTRING(Date_Sent,1,2)as int) + '/' +
CAST(SUBSTRING(date_sent,4,2) as int) + '/'+CAST(SUBSTRING(DATE_SENT,7,4) as int)

From MyTable

It is not working, anybody can give me some advise!

thanks!

DECLARE @.Date_Sent CHAR(10)
SET @.Date_Sent = '28/02/2004'
SELECT CONVERT(DATETIME, @.Date_Sent, 103)|||

Try:

SELECT

Cast(SUBSTRING(Date_Sent,4,2) + '/' +
SUBSTRING(date_sent,1,2) + '/'+SUBSTRING(DATE_SENT,7,4) as datetime)

|||

The solution fromPDraigh worked for me .

Thanks

really should have designed database to use datetime in first place , but this is a nice work around

|||

Strange !! it gave me an error in the SQL statement !!

my field is a varchar

Convert Varchar into Int

Hi

I have imported some varchar figures.

such as 0.050 (we'll call this Field)

I need to convert them into int to be used for calculations.

when I do

CAST(Field as int)

I get...

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '0.050' to a column of data type int.

Normally I dont get this error?

Anyone?

Quote:

Originally Posted by flickimp

Hi

I have imported some varchar figures.

such as 0.050 (we'll call this Field)

I need to convert them into int to be used for calculations.

when I do

CAST(Field as int)

I get...

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '0.050' to a column of data type int.

Normally I dont get this error?

Anyone?

INT datatype (SQL Server Books online (BOL)

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

therefore... cast(field as decimal(18,3))
or
cast(field as numeric(18,3))

will return

.050

INT will round to the nearest whole number.

If calculating with decimalised data, numeric data precison and scale will demand decimal or numeric datatype

cast in itself cannot explicitly cast a 0.050 'varchar' value to a whole number
therefore inserting any 0.050 value in an integer would round it down to zero (the nearest whole number)


Regards

Jim :)

Convert Time

Hi,

Hopefully this is an easy one. I need a function to call from my page footer that takes the NOW date/time value and converts it from Eastern Standard Time to Mountain Standard Time. (My server is on EST time and my users are all on MST time) Of course the function needs to handle daylight savings as well.

thanks!

Why don't you just subtract two hours from the NOW date/time value to get MST.....

Regardless of where your server is, you should be able to convert the time value to any timezone in the world.

|||

That would work until daylight savings...then it's 3 hours difference. I was really hoping to get a custom function from someone...Thx

|||

So you're saying that NOW doesn't adjust for daylight savings?

|||

I'm saying the following:

If NOW is 2pm EST then MST is 12PM ( subtracting 2 hours from EST will work)

When daylight savings kicks in then NOW is 3pm EST and MST is still 12pm (subtracting 2 hours from EST give me 1pm which is incorrect.)

When I say MST I mean arizona time which does not change for daylight savings.

Hope that makes sense..

|||

Yes that makes sense. After a brief research of the forums and google, I've turned up empty handed. Hopefully someone else knows how to do it.

I am assuming that you can't simply change your server's time to MST.

|||

R.K.S. wrote:

I'm saying the following:

If NOW is 2pm EST then MST is 12PM ( subtracting 2 hours from EST will work)

When daylight savings kicks in then NOW is 3pm EST and MST is still 12pm (subtracting 2 hours from EST give me 1pm which is incorrect.)

When I say MST I mean arizona time which does not change for daylight savings.

Hope that makes sense..

You are not looking for DateTime but TimeZone there are only work around solutions but coming in VS2008 try the thread below for your options.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1860968&SiteID=1

|||

Thanks Caddre, Not the answer i was hoping for but at least i know i wasn't overlooking something simple.

What about this: I already have a function written in C# that will do the conversion. Can't i call a custom function like this in a page footer? Can the custom function exist in an assembly or in the custom code window...

Thx

|||

The Report properties includes code box what I don't know is if you can run it in a footer, you could also check the ReportViewer control it may have a way to use .NET code much easier than normal reports.

|||

If anyone is interested you can use this function to convert to whatever local time your users are on (assuming all your users are on a single timezone) So below, my server is EST and my users are all on MST

Public Shared Function AdjustDateTime(ByVal dt as DateTime)

Dim tz as System.TimeZone
tz = System.TimeZone.CurrentTimeZone

Dim utc as System.DateTime
utc = tz.ToUniversalTime(dt)

'Convert EST to MST
if Not tz.IsDaylightSavingTime(dt) then
AdjustDateTime =utc.ToLocalTime().AddHours(-2)
else
AdjustDateTime =utc.ToLocalTime().AddHours(-3)
end if

End Function

Convert Time

Hi,

Hopefully this is an easy one. I need a function to call from my page footer that takes the NOW date/time value and converts it from Eastern Standard Time to Mountain Standard Time. (My server is on EST time and my users are all on MST time) Of course the function needs to handle daylight savings as well.

thanks!

Why don't you just subtract two hours from the NOW date/time value to get MST.....

Regardless of where your server is, you should be able to convert the time value to any timezone in the world.

|||

That would work until daylight savings...then it's 3 hours difference. I was really hoping to get a custom function from someone...Thx

|||

So you're saying that NOW doesn't adjust for daylight savings?

|||

I'm saying the following:

If NOW is 2pm EST then MST is 12PM ( subtracting 2 hours from EST will work)

When daylight savings kicks in then NOW is 3pm EST and MST is still 12pm (subtracting 2 hours from EST give me 1pm which is incorrect.)

When I say MST I mean arizona time which does not change for daylight savings.

Hope that makes sense..

|||

Yes that makes sense. After a brief research of the forums and google, I've turned up empty handed. Hopefully someone else knows how to do it.

I am assuming that you can't simply change your server's time to MST.

|||

R.K.S. wrote:

I'm saying the following:

If NOW is 2pm EST then MST is 12PM ( subtracting 2 hours from EST will work)

When daylight savings kicks in then NOW is 3pm EST and MST is still 12pm (subtracting 2 hours from EST give me 1pm which is incorrect.)

When I say MST I mean arizona time which does not change for daylight savings.

Hope that makes sense..

You are not looking for DateTime but TimeZone there are only work around solutions but coming in VS2008 try the thread below for your options.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1860968&SiteID=1

|||

Thanks Caddre, Not the answer i was hoping for but at least i know i wasn't overlooking something simple.

What about this: I already have a function written in C# that will do the conversion. Can't i call a custom function like this in a page footer? Can the custom function exist in an assembly or in the custom code window...

Thx

|||

The Report properties includes code box what I don't know is if you can run it in a footer, you could also check the ReportViewer control it may have a way to use .NET code much easier than normal reports.

|||

If anyone is interested you can use this function to convert to whatever local time your users are on (assuming all your users are on a single timezone) So below, my server is EST and my users are all on MST

Public Shared Function AdjustDateTime(ByVal dt as DateTime)

Dim tz as System.TimeZone
tz = System.TimeZone.CurrentTimeZone

Dim utc as System.DateTime
utc = tz.ToUniversalTime(dt)

'Convert EST to MST
if Not tz.IsDaylightSavingTime(dt) then
AdjustDateTime =utc.ToLocalTime().AddHours(-2)
else
AdjustDateTime =utc.ToLocalTime().AddHours(-3)
end if

End Function

convert stored procedures returned output to varchar from int?

I have 1 files, one is .sql and another is stored procedure. SQL file will call the stored procedure by passing the variables setup in the SQL file. However, everything ran well except at the end, I try to get the return value from SP to my SQL file ... which will send notification email. But, I get the following msg ... I am not sure how to fix this ... help!!! :(

Syntax error converting the varchar value 'ABC' to a column of data type int.

SQL file

======================

DECLARE @.S AS VARCHAR(1000)

EXEC @.S = PDT.DBO.SP_RPT
'ABC'

SELECT CONTENT = @.S -- this is the value I am trying to pass as content of the email

EXEC PRODUCTION.DBO.SENDEMAIL'xxx@.hotmail.com', 'Notification', @.S

======================

Stored Procedure

======================

CREATE procedure sp_RPT
( @.array varchar(1000) )
AS

DECLARE @.content AS VARCHAR(2000)

SET @.content = 'RPT: ' + @.array + ' loaded successfully '

SET @.array = 'ABC'
RETURN CONVERT(VARCHAR(1000),@.array)

GO

try

cast( @.array as varchar(1000))

Hope this help

|||Don't use RETURN values in that manner. Either use an output parameter, or a resultset. RETURN values should be used as a control channel, not as a data channel; RETURN's purpose is to terminate control of a stored procedure immediately and also to report on how control was terminated (0 = normal/good, anything else is abnormal/bad).

Convert Int to Time

I have a DB that gets telephone call information from a telephone switch. We use 3rd part software and a db to capture all of this. However for some reason most of the fields are stored as int DateTime is stored as an int. I am able to convert most of the date field from int by doing select convert(datetime,convert(varchar(8), localday)) that works fine the int are in the db as 20061029 for 10-29-2006.
How ever I can't convert the columns starttime and endtime. They are also stored as int but
They don't seem to store a date.
For instance for the date 9/29/2006 the start time of one particular call is
1159533056 and the endtime is 1159533264 All i need to get from this is the time since I already have the date from another column. I can't figure out what this data represents.
How can I convert this to datetime?

Thanks!

Quote:

"The datetime and smalldatetime data types are stored internally as integers. The datetime data type is stored as a pair of four-bytes integers, which together represent the number of milliseconds since midnight of January 1, 1753. The first four bytes store the date, and the second four bytes store the time. The smalldatetime data type is stored as a pair of two-byte integers, which together represent the number of minutes since midnight on January 1, 1900. The first two bytes store the date, and the second two bytes store the time."

Also, check out this: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp (see the first table on that page).

So, if your columns' data types are datetime or smalldatetime (but not "int", which would be bizarre), you don't need to concern yourself with converting it manually and/or trying to figure out what exactly this integer means. Just call the appropriate version of CONVERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp).

|||

It looks to me like your integers are storing number of seconds since the epoch, or 1 January 1970. This is pretty common on UNIX systems. (Cf. http://en.wikipedia.org/wiki/Unix_time)

Try:

declare @.d1 datetime
declare @.d2 datetime
set @.d1 = dateadd(second, 1159533056, '1 January 1970')
set @.d2 = dateadd(second, 1159533264, '1 January 1970')
print @.d1
print @.d2

Result:

Sep 29 2006 12:30PM
Sep 29 2006 12:34PM

Cheers,
-Isaac

|||

Great that did it. yes they are stored as integers. I hate this db everything that is not text is stored as INT I mean Everything.
I have had to create several calculated fields for them. It's 3rd party db if i modify existing colums it won't function.

What would be the best way to create a calculated field for this data?
Thanks again

|||nevermind i got it by doing this...

alter TABLE [CDRMAin] add
[starttimetimecalc] AS dateadd(second, endtime, '1 January 1970')|||I just realized that this code is giving me the proper date from my date that is stored as an int, but it is not giving me the proper time
for this datetime as an int 1163517315
i get 2006-11-14 15:15:15.000, the correct time should be 10:16 am
the code i used to create my table is
alter TABLE [CDRMAin] add
[starttimetimecalc] AS dateadd(second, endtime, '1 January 1970')

how can i correct this.
thanks

Convert Int to Time

I have a DB that gets telephone call information from a telephone switch. We use 3rd part software and a db to capture all of this. However for some reason most of the fields are stored as int DateTime is stored as an int. I am able to convert most of the date field from int by doing select convert(datetime,convert(varchar(8), localday)) that works fine the int are in the db as 20061029 for 10-29-2006.
How ever I can't convert the columns starttime and endtime. They are also stored as int but
They don't seem to store a date.
For instance for the date 9/29/2006 the start time of one particular call is
1159533056 and the endtime is 1159533264 All i need to get from this is the time since I already have the date from another column. I can't figure out what this data represents.
How can I convert this to datetime?

Thanks!

Quote:

"The datetime and smalldatetime data types are stored internally as integers. The datetime data type is stored as a pair of four-bytes integers, which together represent the number of milliseconds since midnight of January 1, 1753. The first four bytes store the date, and the second four bytes store the time. The smalldatetime data type is stored as a pair of two-byte integers, which together represent the number of minutes since midnight on January 1, 1900. The first two bytes store the date, and the second two bytes store the time."

Also, check out this: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp (see the first table on that page).

So, if your columns' data types are datetime or smalldatetime (but not "int", which would be bizarre), you don't need to concern yourself with converting it manually and/or trying to figure out what exactly this integer means. Just call the appropriate version of CONVERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp).

|||

It looks to me like your integers are storing number of seconds since the epoch, or 1 January 1970. This is pretty common on UNIX systems. (Cf. http://en.wikipedia.org/wiki/Unix_time)

Try:

declare @.d1 datetime
declare @.d2 datetime
set @.d1 = dateadd(second, 1159533056, '1 January 1970')
set @.d2 = dateadd(second, 1159533264, '1 January 1970')
print @.d1
print @.d2

Result:

Sep 29 2006 12:30PM
Sep 29 2006 12:34PM

Cheers,
-Isaac

|||

Great that did it. yes they are stored as integers. I hate this db everything that is not text is stored as INT I mean Everything.
I have had to create several calculated fields for them. It's 3rd party db if i modify existing colums it won't function.

What would be the best way to create a calculated field for this data?
Thanks again

|||nevermind i got it by doing this...

alter TABLE [CDRMAin] add
[starttimetimecalc] AS dateadd(second, endtime, '1 January 1970')|||I just realized that this code is giving me the proper date from my date that is stored as an int, but it is not giving me the proper time
for this datetime as an int 1163517315
i get 2006-11-14 15:15:15.000, the correct time should be 10:16 am
the code i used to create my table is
alter TABLE [CDRMAin] add
[starttimetimecalc] AS dateadd(second, endtime, '1 January 1970')

how can i correct this.
thanks

Convert datetime to seconds

Hello. I have a datetime field that stores data that looks like:
1899-12-30 00:01:28.000

Unfortunately this column represents a Call Duration (in seconds). For example, in the above data the call lasted 1 minute and 28 seconds.

I can't seem to figure out how to either
1) return only the last eight characters of this field (in excel it would be the equivelent of MID, RIGHT, LEFT). From here I was thinking that I could use CONVERT?
OR
2) convert from date/time to int which represents total seconds.

Any help is greatly appreciated!
-KristinaWhere in foo-floggy did you get the 1899-12-30 ?!?! I've seen lots of strange values pop up in applications, but that's a new one to me! There are two different approaches to your problems (one for each problem, of course). They are:DECLARE @.d DATETIME
SET @.d = '1899-12-30 00:01:28.000'

SELECT DateDiff(second, '00:00', Convert(VARCHAR(30), @.d, 14))
, Right(Convert(VARCHAR(12), @.d, 14), 8)-PatP|||taking the hint that in excel it would be the equivelent of MID, RIGHT, LEFT you might be using a MS product what are you accessing this database with. I'd be tempted to stick this into a formatting funtion. In Access SQL you can put the function into the SQL to create a derived field in the query, you may be able to do the same or similar depending on the server you are using.

However it does sound as if the data model is either incorrectly specified or populated. What is it that is setting the day/date component. If that is consistent then you may be OK as you are. However if the vendor decides to change the baseline method between versions (its hardly unkown for suppliers to be so creative) then you could have a problem.

Personally I'd want to tie down the input so that it was only Hours/minutes/seconds, which I if couldn't get a stable method of storing that in the DB I'd probably resort to storing it as a long integer, and writing an encoding / decoding function to het back to the DD:MM:MM:SS value you expect to see.|||pat, 1899-12-30 is the base date for datetime values supplied without a date component in either sql server or access

kristina, go with option 2)

time durations should never be stored as datetimes|||1899-12-30Of the two values I've seen used as a base date, that one hasn't ever been a choice. What version are you using?

-PatP|||me? i'm using 08.00.0760|||Interesting. The two choices that I've seen are 1900-1-0 and 1904-1-0, which compute as 1899-12-31 and 1903-12-31. If I try to enter 1899-12-30 as a date, neither MS-Excel nor MS-Access recognize it as such, and they treat it as raw text.

Maybe it is something in the international versions? I'll have to try it on a UK machine if I have a chance tomorrow.

-PatP