Converting C++ Unix time_t Julian date to SQL date

I've just been trying to do this. I looked on Google and it seems to
be a common problem with no obvious solution. I've seen various
solutions which don't seem exactly elegant, so I figured I'd post the
solution I came up with. It's effectively a single line solution,
albeit with various embedded calls. It makes an adjustment for the
local timezone too!
DECLARE @.time_t INT
SET @.time_t = 1090834321 /* 10:32am, July 26th 2004 BST */
DECLARE @.timestamp DATETIME
SET @.timestamp = dateadd (ss, datediff (ss, GetUTCDate(), GetDate()),
dateadd (ss, @.time_t, '19700101'))
PRINT @.timestamp
Hope that helps. If there's a problem with it, let me know!
andytHi Andy,
I have received a data file and it has julian dates. How Do I convert them
to normal dates? I tried this code and replaced the variable with the date I
received, and All it did was give me the same date with different times.
Any ideas? What does the hardcoded '1970-01-01' do?
Thanks
Noma-Gcina
nmtshontshi@.deloitte.com
"Andy Turner" wrote:

> I've just been trying to do this. I looked on Google and it seems to
> be a common problem with no obvious solution. I've seen various
> solutions which don't seem exactly elegant, so I figured I'd post the
> solution I came up with. It's effectively a single line solution,
> albeit with various embedded calls. It makes an adjustment for the
> local timezone too!
>
> DECLARE @.time_t INT
> SET @.time_t = 1090834321 /* 10:32am, July 26th 2004 BST */
> DECLARE @.timestamp DATETIME
> SET @.timestamp = dateadd (ss, datediff (ss, GetUTCDate(), GetDate()),
> dateadd (ss, @.time_t, '19700101'))
> PRINT @.timestamp
>
> Hope that helps. If there's a problem with it, let me know!
>
> andyt
>|||The Unix timestamp value represents the number of seconds since 1970-01-01.
You can convert it like this:
DECLARE @.ts INTEGER
SET @.ts = 1098230400
SELECT DATEADD(SECOND,@.ts,'19700101')
(This isn't a Julian Date BTW)
David Portas
SQL Server MVP
--

Converting C++ Unix time_t Julian date to SQL date

I've just been trying to do this. I looked on Google and it seems to
be a common problem with no obvious solution. I've seen various
solutions which don't seem exactly elegant, so I figured I'd post the
solution I came up with. It's effectively a single line solution,
albeit with various embedded calls. It makes an adjustment for the
local timezone too!
DECLARE @.time_t INT
SET @.time_t = 1090834321/* 10:32am, July 26th 2004 BST */
DECLARE @.timestamp DATETIME
SET @.timestamp = dateadd (ss, datediff (ss, GetUTCDate(), GetDate()),
dateadd (ss, @.time_t, '19700101'))
PRINT @.timestamp
Hope that helps. If there's a problem with it, let me know!
andyt
Hi Andy,
I have received a data file and it has julian dates. How Do I convert them
to normal dates? I tried this code and replaced the variable with the date I
received, and All it did was give me the same date with different times.
Any ideas? What does the hardcoded '1970-01-01' do?
Thanks
Noma-Gcina
nmtshontshi@.deloitte.com
"Andy Turner" wrote:

> I've just been trying to do this. I looked on Google and it seems to
> be a common problem with no obvious solution. I've seen various
> solutions which don't seem exactly elegant, so I figured I'd post the
> solution I came up with. It's effectively a single line solution,
> albeit with various embedded calls. It makes an adjustment for the
> local timezone too!
>
> DECLARE @.time_t INT
> SET @.time_t = 1090834321/* 10:32am, July 26th 2004 BST */
> DECLARE @.timestamp DATETIME
> SET @.timestamp = dateadd (ss, datediff (ss, GetUTCDate(), GetDate()),
> dateadd (ss, @.time_t, '19700101'))
> PRINT @.timestamp
>
> Hope that helps. If there's a problem with it, let me know!
>
> andyt
>
|||The Unix timestamp value represents the number of seconds since 1970-01-01.
You can convert it like this:
DECLARE @.ts INTEGER
SET @.ts = 1098230400
SELECT DATEADD(SECOND,@.ts,'19700101')
(This isn't a Julian Date BTW)
David Portas
SQL Server MVP

Converting a RS 2000 reporting solution to RS 2005

I currently have a RS 2000 solution running on SQL 2000 database, AS
2000 and RS 2000.
I want to migrate this solution to the same schema running on a SQL
2005 database and AS 2005 with RS 2005.
Please kindly explain the best way I can accomplish this migration.
Thanks
KarenHi Karen,
I had a same problem few months before and I had to made it manually using
RSScripter (http://www.sqldbatips.com/samples/code/RSScripter/RSScripter.zip) for
collecting items and then manually upgrade and deploy with Visual Studio 2005.
Kind regards,
Zoran
"KarenM" <karenmiddleol@.yahoo.com> wrote in news:1159353990.309207.67240
@.m7g2000cwm.googlegroups.com:
> I currently have a RS 2000 solution running on SQL 2000 database, AS
> 2000 and RS 2000.
> I want to migrate this solution to the same schema running on a SQL
> 2005 database and AS 2005 with RS 2005.
> Please kindly explain the best way I can accomplish this migration.
> Thanks
> Karen
>

Convert MS IIF STMT to SQL Server 2005

found solution for this problem just now
thank you very much

Quote:

Originally Posted by blaqusql

found solution for this problem just now
thank you very much

if it's in a query, try CASE ... WHEN

Convert EXISTS to JOIN or?

Hello!
I have a query I cannot find a solution to.
Structure: Nodes transferring units. One nodes table with
info. It is related to itself, so that a node can have many
sourcenodes.
.--.
|xfers|--+
`--' |
| |
| .--+--. 1.* .--.
|--+nodes|--.intervals|
`--' `--'
The relation table is xfers (transfers). Each node has an
interval of a basic unit, a lot and start/stop.
I want access to:
- all nodes
- all sourcenodes that have an intersecting interval
(i e an interval with same lot and an intersecting
interval with the targetnode)
- the first matching intersecting interval for each node
I've been testing some different things but have not gotten
a working solution. This gives the correct rows but does
not get me access to the first matching intersecting
interval (T-SQL):
*** START SQL CODE:
-- Desired rows, yet I cannot access the matching row in
the EXISTS test
SELECT
nodes.nodeid, nodes.nodename,
source_nodeid, volume
FROM nodes
LEFT OUTER JOIN xfers
ON target_nodeid = nodes.nodeid AND EXISTS
( SELECT *
FROM intervals sourceIntervals
JOIN intervals nodeIntervals
ON
nodeIntervals.lot = sourceIntervals.lot
AND dbo.Intersection( nodeIntervals.start,
nodeIntervals.stop, sourceIntervals.start,
sourceIntervals.stop ) = 1
WHERE
sourceIntervals.nodes_nodeid = xfers.source_nodeid
AND nodeIntervals.nodes_nodeid = xfers.target_nodeid
)
LEFT OUTER JOIN nodes sourceNodes
ON sourceNodes.nodeid = source_nodeid
*** END SQL CODE
Notice use of intersection function, below.
No success in remaking this as a join so far.
Here is a setup script:
*** START SQL CODE:
CREATE TABLE nodes ( nodeid INT, nodename VARCHAR(32),
PRIMARY KEY( nodeid ) )
CREATE TABLE xfers ( target_nodeid INT, source_nodeid INT,
volume INT, PRIMARY KEY( source_nodeid, target_nodeid ) )
CREATE TABLE intervals ( nodes_nodeid INT, lot VARCHAR(3),
lotcount INT, start INT, stop INT, PRIMARY KEY(
nodes_nodeid, lot, lotcount ) )
go
INSERT INTO nodes
SELECT 12, 'Run A' UNION
SELECT 25, 'Switch A B' UNION
SELECT 26, 'Continue B' UNION
SELECT 57, 'Mix'
go
INSERT INTO intervals
SELECT 12, 'A', 1, 1, 180 UNION
SELECT 25, 'A', 1, 181, 195 UNION
SELECT 25, 'B', 1, 1, 21 UNION
SELECT 26, 'B', 1, 22, 492 UNION
SELECT 57, 'A', 1, 1, 195 UNION
SELECT 57, 'B', 1, 1, 492
go
INSERT INTO xfers
SELECT 57, 12, 15 UNION
SELECT 57, 25, 3 UNION
SELECT 57, 26, 28
go
*** END SQL CODE
Intersection function:
*** START SQL CODE:
CREATE FUNCTION Intersection ( @.startA int, @.stopA int,
@.startB int, @.stopB int )
RETURNS bit
AS
BEGIN
DECLARE @.rValue bit
IF
@.startA BETWEEN @.startB AND @.stopB
OR
@.stopA BETWEEN @.startB AND @.stopB
SET @.rValue = 1
ELSE
SET @.rValue = 0
RETURN ( @.rValue )
END
go
*** END SQL CODE
//mawimawi
What do you want to return? What is an output?
I'd rewrite a little bit the function
CREATE FUNCTION Intersection ( @.startA int, @.stopA int,
@.startB int, @.stopB int )
RETURNS bit
AS
BEGIN
RETURN CASE
WHEN @.startA BETWEEN @.startB AND @.stopB THEN 1 ELSE 0 END
END
go
"mawi" <anonymous@.discussions.microsoft.com> wrote in message
news:24d101c5344c$d11c2a50$a401280a@.phx.gbl...
> Hello!
> I have a query I cannot find a solution to.
> Structure: Nodes transferring units. One nodes table with
> info. It is related to itself, so that a node can have many
> sourcenodes.
> .--.
> |xfers|--+
> `--' |
> | |
> | .--+--. 1.* .--.
> |--+nodes|--.intervals|
> `--' `--'
> The relation table is xfers (transfers). Each node has an
> interval of a basic unit, a lot and start/stop.
> I want access to:
> - all nodes
> - all sourcenodes that have an intersecting interval
> (i e an interval with same lot and an intersecting
> interval with the targetnode)
> - the first matching intersecting interval for each node
> I've been testing some different things but have not gotten
> a working solution. This gives the correct rows but does
> not get me access to the first matching intersecting
> interval (T-SQL):
> *** START SQL CODE:
> -- Desired rows, yet I cannot access the matching row in
> the EXISTS test
> SELECT
> nodes.nodeid, nodes.nodename,
> source_nodeid, volume
> FROM nodes
> LEFT OUTER JOIN xfers
> ON target_nodeid = nodes.nodeid AND EXISTS
> ( SELECT *
> FROM intervals sourceIntervals
> JOIN intervals nodeIntervals
> ON
> nodeIntervals.lot = sourceIntervals.lot
> AND dbo.Intersection( nodeIntervals.start,
> nodeIntervals.stop, sourceIntervals.start,
> sourceIntervals.stop ) = 1
> WHERE
> sourceIntervals.nodes_nodeid = xfers.source_nodeid
> AND nodeIntervals.nodes_nodeid = xfers.target_nodeid
> )
> LEFT OUTER JOIN nodes sourceNodes
> ON sourceNodes.nodeid = source_nodeid
> *** END SQL CODE
> Notice use of intersection function, below.
> No success in remaking this as a join so far.
> Here is a setup script:
> *** START SQL CODE:
> CREATE TABLE nodes ( nodeid INT, nodename VARCHAR(32),
> PRIMARY KEY( nodeid ) )
> CREATE TABLE xfers ( target_nodeid INT, source_nodeid INT,
> volume INT, PRIMARY KEY( source_nodeid, target_nodeid ) )
> CREATE TABLE intervals ( nodes_nodeid INT, lot VARCHAR(3),
> lotcount INT, start INT, stop INT, PRIMARY KEY(
> nodes_nodeid, lot, lotcount ) )
> go
> INSERT INTO nodes
> SELECT 12, 'Run A' UNION
> SELECT 25, 'Switch A B' UNION
> SELECT 26, 'Continue B' UNION
> SELECT 57, 'Mix'
> go
> INSERT INTO intervals
> SELECT 12, 'A', 1, 1, 180 UNION
> SELECT 25, 'A', 1, 181, 195 UNION
> SELECT 25, 'B', 1, 1, 21 UNION
> SELECT 26, 'B', 1, 22, 492 UNION
> SELECT 57, 'A', 1, 1, 195 UNION
> SELECT 57, 'B', 1, 1, 492
> go
> INSERT INTO xfers
> SELECT 57, 12, 15 UNION
> SELECT 57, 25, 3 UNION
> SELECT 57, 26, 28
> go
> *** END SQL CODE
> Intersection function:
> *** START SQL CODE:
> CREATE FUNCTION Intersection ( @.startA int, @.stopA int,
> @.startB int, @.stopB int )
> RETURNS bit
> AS
> BEGIN
> DECLARE @.rValue bit
> IF
> @.startA BETWEEN @.startB AND @.stopB
> OR
> @.stopA BETWEEN @.startB AND @.stopB
> SET @.rValue = 1
> ELSE
> SET @.rValue = 0
> RETURN ( @.rValue )
> END
> go
> *** END SQL CODE
> //mawi|||I haven't digested the whole thing, but this line doesn't seem right:
@.startA BETWEEN @.startB AND @.stopB
--^ and --^ @.startA / @.stopA surely?
"mawi" <anonymous@.discussions.microsoft.com> wrote in message
news:24d101c5344c$d11c2a50$a401280a@.phx.gbl...
> Hello!
> I have a query I cannot find a solution to.
> Structure: Nodes transferring units. One nodes table with
> info. It is related to itself, so that a node can have many
> sourcenodes.
> .--.
> |xfers|--+
> `--' |
> | |
> | .--+--. 1.* .--.
> |--+nodes|--.intervals|
> `--' `--'
> The relation table is xfers (transfers). Each node has an
> interval of a basic unit, a lot and start/stop.
> I want access to:
> - all nodes
> - all sourcenodes that have an intersecting interval
> (i e an interval with same lot and an intersecting
> interval with the targetnode)
> - the first matching intersecting interval for each node
> I've been testing some different things but have not gotten
> a working solution. This gives the correct rows but does
> not get me access to the first matching intersecting
> interval (T-SQL):
> *** START SQL CODE:
> -- Desired rows, yet I cannot access the matching row in
> the EXISTS test
> SELECT
> nodes.nodeid, nodes.nodename,
> source_nodeid, volume
> FROM nodes
> LEFT OUTER JOIN xfers
> ON target_nodeid = nodes.nodeid AND EXISTS
> ( SELECT *
> FROM intervals sourceIntervals
> JOIN intervals nodeIntervals
> ON
> nodeIntervals.lot = sourceIntervals.lot
> AND dbo.Intersection( nodeIntervals.start,
> nodeIntervals.stop, sourceIntervals.start,
> sourceIntervals.stop ) = 1
> WHERE
> sourceIntervals.nodes_nodeid = xfers.source_nodeid
> AND nodeIntervals.nodes_nodeid = xfers.target_nodeid
> )
> LEFT OUTER JOIN nodes sourceNodes
> ON sourceNodes.nodeid = source_nodeid
> *** END SQL CODE
> Notice use of intersection function, below.
> No success in remaking this as a join so far.
> Here is a setup script:
> *** START SQL CODE:
> CREATE TABLE nodes ( nodeid INT, nodename VARCHAR(32),
> PRIMARY KEY( nodeid ) )
> CREATE TABLE xfers ( target_nodeid INT, source_nodeid INT,
> volume INT, PRIMARY KEY( source_nodeid, target_nodeid ) )
> CREATE TABLE intervals ( nodes_nodeid INT, lot VARCHAR(3),
> lotcount INT, start INT, stop INT, PRIMARY KEY(
> nodes_nodeid, lot, lotcount ) )
> go
> INSERT INTO nodes
> SELECT 12, 'Run A' UNION
> SELECT 25, 'Switch A B' UNION
> SELECT 26, 'Continue B' UNION
> SELECT 57, 'Mix'
> go
> INSERT INTO intervals
> SELECT 12, 'A', 1, 1, 180 UNION
> SELECT 25, 'A', 1, 181, 195 UNION
> SELECT 25, 'B', 1, 1, 21 UNION
> SELECT 26, 'B', 1, 22, 492 UNION
> SELECT 57, 'A', 1, 1, 195 UNION
> SELECT 57, 'B', 1, 1, 492
> go
> INSERT INTO xfers
> SELECT 57, 12, 15 UNION
> SELECT 57, 25, 3 UNION
> SELECT 57, 26, 28
> go
> *** END SQL CODE
> Intersection function:
> *** START SQL CODE:
> CREATE FUNCTION Intersection ( @.startA int, @.stopA int,
> @.startB int, @.stopB int )
> RETURNS bit
> AS
> BEGIN
> DECLARE @.rValue bit
> IF
> @.startA BETWEEN @.startB AND @.stopB
> OR
> @.stopA BETWEEN @.startB AND @.stopB
> SET @.rValue = 1
> ELSE
> SET @.rValue = 0
> RETURN ( @.rValue )
> END
> go
> *** END SQL CODE
> //mawi|||Mercury wrote on Tue, 29 Mar 2005 23:59:16 +1200:

> I haven't digested the whole thing, but this line doesn't seem right:
> @.startA BETWEEN @.startB AND @.stopB
> --^ and --^ @.startA / @.stopA surely?
But that would result in the clause always being true.
The function appears to return a 1 if the A values are between the B values.
Dan