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

convert unix timestamp to UTC

Hi

Is there a way in T-SQL (Server 2005) to convert unix timestamp to UTC

So want to do this:

1189481763.61 -> Tue, 11 Sep 2007 03:36:03 UTC(or any UTC format)

Thanks

You can use the DATEADD function to add the unix date time value to Jan 1 1970 12:00:00 AM.

Code Snippet

DECLARE @.unixDate FLOAT,

@.tsqlDate DATETIME

SET @.unixDate = 1189481763.61

SET @.tsqlDate = '01/01/1970 00:00:00 AM'

SET @.tsqlDate = DATEADD(ss, @.unixDate, @.tsqlDate)

PRINT @.tsqlDate

|||

Hi

i was planning on doing that but but the problem with that is :

number

Is the value used to increment datepart. If you specify a value that is not an integer, the fractional part of the value is discarded. For example, if you specify day for datepart and1.75 for number, date is incremented by 1.

So it will truncate 1189481763.61 to 1189481763

I was looking for a work around

Thanks

|||

Code Snippet

DECLARE @.unixDate float,
@.tsqlDate DATETIME
SET @.unixDate = 1189481763.61

SET @.tsqlDate = '01/01/1970 00:00:00 AM'
SET @.tsqlDate = DATEADD(ss, @.unixDate, @.tsqlDate)
PRINT convert(varchar(15),@.tsqlDate,114)
-- Now convert the fractional part into millseconds and add to the date
SET @.tsqlDate = DATEADD(ms, (@.UnixDate - cast(@.unixDate as int)) *1000, @.tsqlDate)
PRINT convert(varchar(15),@.tsqlDate,114)

Convert from unix time

One of my tables has an open_date and close_date that are stored in unix time. I added two other colums z_opendate and z_closedate and I now would like to convert open_date and close_date from unix time and populate the new colums I added.
Can anyone give me some insite on how to do this?
Thanks.
Eric,
Unix time is typically represented as the number of non-leap seconds
after midnight, January 1, 1970. If that's what you have (integers a
bit larger than 1,000,000,000), try using dateadd(second, open_date,
'19700101') to get a datetime representation of open_date, for example.
Steve Kass
Drew University
Eric wrote:

>One of my tables has an open_date and close_date that are stored in unix time. I added two other colums z_opendate and z_closedate and I now would like to convert open_date and close_date from unix time and populate the new colums I added.
>Can anyone give me some insite on how to do this?
>
>Thanks.
>
>

convert data file type from unix to pc using stored procedure?

Hi, I have a script written in ASP to load data file (.csv) to ms sql. In the script, I have a portion of script looks like taht :

....

Do While NOT oInFile.AtEndOfStream
oOutFile.WriteLine Replace(oInFile.Readline, chr(13), vbcrlf)
Loop

....

After that, I will use a BULK INSERT to input data to ms sql.

I am wondering how do I convert each row (data) to vbcrlf in Stored Procedure? Coz' I did not compose the convertion part, and I got no error when running BULK INSERT, but no rows are inserted :( HELP!!!!

I guess it's because the file is not being converted into a correct format??

Can you give more information? e.g.: sample text used in BULK INSERT, BULK INSERT command you're using, and the schema of the destination table.