3

I need the following query below converted for SQL Server 2005, so please let me know what I should change since SQL Server doesn't support DATE_FORMAT()

How it should look converted in SQL Server so I can display proper date on my website instead of timestamp (example: 1382016108 to be 2013-06-22 10:53:22) ?

SELECT DATE_FORMAT(TimeTransfer,'%Y-%m-%d %h:%i:%s')
FROM PREMIUM where strAccountID = ?, $_SESSION['strAccountID']);

EDIT: I checked the CONVERT() function but couldn't find out how it should be exactly in my case with my query. Help is greatly appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
KOMban
  • 31
  • 3
  • possible duplicate of [Convert UTC Milliseconds to DATETIME in SQL server](http://stackoverflow.com/questions/12524537/convert-utc-milliseconds-to-datetime-in-sql-server) – Ocaso Protal Oct 17 '13 at 12:33
  • 1
    What format is your timestamp in? It doesn't look big enough to be in UTC milliseconds, so what is it? – RBarryYoung Oct 17 '13 at 12:39
  • Example: 1380319719 that is directly from my table. – KOMban Oct 17 '13 at 12:49
  • @KOMban That doesn't tell us anything. Tell us what format or scale or units your Timestamp is in. – RBarryYoung Oct 17 '13 at 12:51
  • TimeTransfer (int) on database and from php its saved using time() .. What else I must specify ? Please let me know. – KOMban Oct 17 '13 at 12:56
  • @KOMban Yes, but we know SQL Server, not PHP, so we don't know what that means. If you can tell us what that "timestamp" number represents mathematically or in terms of date and time calculations, then we can tell you how to convert it. – RBarryYoung Oct 17 '13 at 13:02
  • It represents this exact date: Fri, 27 Sep 2013 22:08:39 with timestamp 1380319719 so for example instead of timestamp I need to display it on website like 2013/09/27 22:08:39 – KOMban Oct 17 '13 at 13:04
  • @KOMban Basically we need to know two things: the *scale* and the *base*. The Scale tells us what a single unit in your timestamp represents. In other words, whats the difference between `1382016108` and `1382016109`? Is it one second, one millisecond, one minute, etc.? The Base tells us what date the value `0000000000` represents. In SQL Server it's `1900-1-1`, in Windows its `1899-12-31`, in UTC it's `1970-1-1`. I have tried several combinations of both of these and none seem to work on your timestamp values. – RBarryYoung Oct 17 '13 at 13:09
  • I am sorry but this in the main post was just an example which I typed manually. I showed a proper one from my database here: 1380319719 which converted means 2013/09/27 22:08:39 ... so if its 20 instead of 19 at the end, it will be 22:08:40 seconds in the end instead of 39 – KOMban Oct 17 '13 at 13:12
  • What mysql datetype is your data? Since this doesn't look like [DATE, DATETIME or TIMESTAMP](http://dev.mysql.com/doc/refman/5.0/en/datetime.html)? – Ocaso Protal Oct 17 '13 at 13:25

3 Answers3

1

This will work:

SELECT CONVERT( VARCHAR(20)           -- Make sure the output string is long enough
    ,   CAST('1970-1-1' As DateTime)  -- The Base Date
      + CAST(TimeTransfer As Float)/(24*60*60)
                                      -- Scale UTC (seconds) to TSQL datetime (days)
                                      --  by dividing by the number of seconds in a day
    , 120)                            -- The format that you want
RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • It works but only when I do that directly in MSSQL Management studio, but if I implement it in the script it doesn't work at all. My template show error and it breaks the whole script. Maybe something to trim it ? – KOMban Oct 17 '13 at 14:10
  • "Doesn't work at all" doesn't tell us anything. I suggest that you post the script and the error/problem in another question. – RBarryYoung Oct 17 '13 at 14:16
0

Check the Convert function. It allows you to give format to a datetime value

SELECT CONVERT(VARCHAR(20),TimeTransfer,120) 
FROM PREMIUM
WHERE strAccountID = ?, $_SESSION['strAccountID']);
Jonysuise
  • 1,790
  • 13
  • 11
0

Use CONVERT() function

SELECT CONVERT(varchar(20),TimeTransfer,120)
FROM PREMIUM where strAccountID = ?, $_SESSION['strAccountID']);

Note : This gives time in 24 hour format

See this link for Date formats in SQL Server

Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133