Converting datetime format - 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

General SQL Server Forums

New to SQL Server Programming

Converting datetime format

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2013-08-12 : 14:00:43

I have a datetime field in my table. I call that field with a different software & it comes out in an unexpected format.

My db table stores it in unix format: 2013-08-12 09:29:00.000
But the software pulls it as: 8/12/2013 9:29:00 AM

I know it's possible, but I don't know how. How do I explicitly call it (in SQL) as a unix timestamp. Or how do I convert to a unix timestamp from the available data above? Thanks

-Sergio
I use Microsoft SQL 2008

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-08-12 : 14:04:08

This is a display issue. You will format the data however you want it inside the application. So it depends on the programming language of the application.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SergioM
Posting Yak Master

170 Posts

Posted - 2013-08-12 : 14:17:01

I simplified it by calling it a software, but in reality I'm having this issue with autohotkey. This is a pretty obscure programming language, but it does use the Windows ADO COM objects to read/write to MSSQL. I'm using it as a bridge between my MSSQL db & MySQL db. So I can be sure it's not a display issue.

Is there a way to explicitly call a datetime in Unix format? Or alternatively to convert an unexpected format to unix datetimeformat?

-Sergio
I use Microsoft SQL 2008

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-12 : 14:27:15

quote:
Originally posted by SergioM

I know it's possible, but I don't know how. How do I explicitly call it (in SQL) as a unix timestamp. Or how do I convert to a unix timestamp from the available data above? Thanks

-Sergio
I use Microsoft SQL 2008

Check this site:
http://technet.microsoft.com/en-us/library/aa226054(v=SQL.80).aspx

Use CONVERT() like examples shown below:
[CODE]

DECLARE @DateTime DATETIME = getdate();

SELECT CONVERT(CHAR(30),@DateTime, 120), 120
UNION
SELECT CONVERT(CHAR(30),@DateTime, 109), 109
UNION
SELECT CONVERT(CHAR(30),@DateTime, 111), 111
UNION
SELECT CONVERT(CHAR(30),@DateTime, 112), 112
UNION
SELECT CONVERT(CHAR(30),@DateTime, 126), 126
UNION
SELECT CONVERT(CHAR(30),@DateTime, 103), 103

[/CODE]

SergioM
Posting Yak Master

170 Posts

Posted - 2013-08-13 : 12:09:49

quote:
Originally posted by MuMu88
Use CONVERT() like examples shown below:

Bingo! Thanks! I would never think to convert to a char, but since datatypes are not explicitly cast in autohotkey, it reads it exactly as I need it. Thanks again!

-Sergio
I use Microsoft SQL 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources