SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Converting datetime format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SergioM
Posting Yak Master

163 Posts

Posted - 08/12/2013 :  14:00:43  Show Profile  Reply with Quote
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

USA
37129 Posts

Posted - 08/12/2013 :  14:04:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

SergioM
Posting Yak Master

163 Posts

Posted - 08/12/2013 :  14:17:01  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 08/12/2013 :  14:27:15  Show Profile  Reply with Quote
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:


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
 
Go to Top of Page

SergioM
Posting Yak Master

163 Posts

Posted - 08/13/2013 :  12:09:49  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000