Proper Date Conversion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Proper Date Conversion

Author

Topic

aerosmith
Starting Member

9 Posts

Posted - 2011-03-02 : 11:20:59

Im having the worst time converting the following Unixdate Example(1179180716)to the following date time 5/14/2007 6:11:56 PM ( need Exact Output syntax/Style)

the code im using

replace(convert(varchar(8), (cast(cast(dateadd(ss,Create_Date,'1970-01-01') as int) as datetime)), 10), '-', '/') + ' ' +substring(convert(varchar(20), getdate(), 9), 13, 5) + ' ' + substring(convert(varchar(30), getdate(), 9), 25, 2) as Create_Date,

Anyone able to shed some light? Id appreciate it

aerosmith
Starting Member

9 Posts

Posted - 2011-03-02 : 20:46:13

wondering if its even possible.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-02 : 22:00:19

Is this for a report? It's easier (and better) to do date formatting in the report level rather than at the database level. Why does it need that exact style?

aerosmith
Starting Member

9 Posts

Posted - 2011-03-03 : 08:55:34

its not for a report,
Im actually trying to change an odbc connection that I use.
And thats the way it is formated.
By keeping the exact format i can spend less time updating reports that use a ms sql view that contains this field.

aerosmith
Starting Member

9 Posts

Posted - 2011-03-05 : 09:55:01

or if its possible to convert it to military time.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-05 : 11:33:19

SELECT DateAdd(second, 1179180716, '19700101')

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-05 : 11:45:58

To get the exact formatting you're after:

SELECT	Convert(varchar(2), DATEPART(month, DateAdd(second, 1179180716, '19700101'))) + '/' +
	Convert(varchar(2), DATEPART(day, DateAdd(second, 1179180716, '19700101'))) + '/' +
	Convert(varchar(4), DATEPART(year, DateAdd(second, 1179180716, '19700101'))) + ' ' +
	Convert(varchar(4), DATEPART(hour, DateAdd(second, 1179180716, '19700101'))) + ':' +
	Convert(varchar(4), DATEPART(minute, DateAdd(second, 1179180716, '19700101'))) + ':' +
	Convert(varchar(4), DATEPART(second, DateAdd(second, 1179180716, '19700101')))

Or you can create a function:

CREATE FUNCTION dbo.ConvertUnixDateTime(@dt int)
RETURNS VARCHAR(19)
AS
BEGIN
RETURN	Convert(varchar(2), DATEPART(month, DateAdd(second, @dt, '19700101'))) + '/' +
	Convert(varchar(2), DATEPART(day, DateAdd(second, @dt, '19700101'))) + '/' +
	Convert(varchar(4), DATEPART(year, DateAdd(second, @dt, '19700101'))) + ' ' +
	Convert(varchar(4), DATEPART(hour, DateAdd(second, @dt, '19700101'))) + ':' +
	Convert(varchar(4), DATEPART(minute, DateAdd(second, @dt, '19700101'))) + ':' +
	Convert(varchar(4), DATEPART(second, DateAdd(second, @dt, '19700101')))
END;
GO

And call it like this:

SELECT	dbo.ConvertUnixDateTime(1179180716)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-05 : 11:58:48

After converting from epoch time to SQL datetime as russel indicated, you can convert it to a variety of string formats, all of which are listed here- http://msdn.microsoft.com/en-us/library/ms187928.aspx

None of those are in the exact format you are looking for, but you can take parts from two or three different formats and assemble the parts to get the exact format you are looking for. Many people on this forum will try to dissuade you from doing that, for some very valid reasons and based on their experience and in-depth knowledge of SQL server.

But the choice is yours, and I must admit, I have been guilty of such on occassion. In my case, I stored the date as a string and knew it was a string, so I would cast it to datetime whenever I wanted to sort or filter. But, once my boss accessed the same data, filtered it without casting, got the incorrect results, published the results to the whole world...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-05 : 12:01:23

And, of course, russel did the conversion for you and beat me to it!! :--)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources