How to convert round number to data and time format

convert time field tool
numeric date format
convert text to date in excel
convert numeric date to date
numbers custom date format
convert number to date in excel
excel convert text to date dd/mm/yyyy
how to change date format in numbers ipad

Two Column in table tblpress

Date          Time
20160307      120949
20160307      133427

Need to be select below the format:

07-03-2016 12:09:49 07-03-2016 13:34 27

or

03-March-2016   12:09: 49 PM
03-March-2016   01:34: 27 PM

I think CAST/CONVERT will help you:

SELECT
  CAST('20160307' AS date),
  CAST(STUFF(STUFF('120949',3,0,':'),6,0,':') AS time)

And convert for out:

SELECT
  CONVERT(varchar(20),NormalDate,105) OutDate, -- Italian style
  CONVERT(varchar(20),NormalTime,108) OutTime -- hh:mi:ss
FROM
  (
    SELECT
      CAST([Date] AS date) NormalDate,
      CAST(STUFF(STUFF([Time],3,0,':'),6,0,':') AS time) NormalTime
    FROM YourTable
  ) q

CAST and CONVERT (Transact-SQL)

And you can use FORMAT (Transact-SQL)

SELECT
  FORMAT(GETDATE(),'dd-MM-yyyy'),
  FORMAT(GETDATE(),'HH:mm:ss')

How do I convert numbers to text in Excel automatically? You need to format the cells as time format firstly by selecting cells and right clicking to select Format Cells in the context menu; in the Format Cells dialog selecting Time in Category list, selecting a time type in Type list, and clicking OK. 2. You also can use this formula =MROUND(A2,15/60/24) to round time to nearest minute. 3.

You can try below

select format(cast([Date] as date),'dd-MMMM-yyyy') as [Date],
TIMEFROMPARTS(LEFT([Time],2), SUBSTRING([Time],3,2), RIGHT([Time],2), 0,0) as [Time]

Time values stored in string or numeric format can be converted into a date format. For example, if using a time field of field data type float or double, rounding  Select the cells you want to format. In the Format   sidebar, click the Cell tab, then click the Data Format pop-up menu and choose Date & Time. Click the Date pop-up menu, then choose a format. If you choose None, no date is displayed in the cell, even if a date is entered and used in date and time calculations.

Best way to do it is to create a function :

create FUNCTION [dbo].[udfGetDateTimeFromInteger]
(
   @intDate int,
   @intTime int
)
RETURNS datetime
AS BEGIN
-- Declare the return variable here
DECLARE @DT_datetime datetime = NULL,
  @str_date varchar(11),
  @str_time varchar(8)
if(@intDate is not null and @intDate > 0)
begin
  select @str_date = CAST( cast(@intDate as varchar(8)) AS date)
  if @intTime=0
     select @str_time ='000000'
  else
     select @str_time = right('0'+CONVERT(varchar(11),@intTime),6)
  select @str_time = 
   SUBSTRING(@str_time,1,2)+':'+SUBSTRING(@str_time,3,2)+':'+SUBSTRING(@str_time,5,2)

  select @DT_datetime = CAST(@str_date+' '+@str_time as datetime)
 end
 -- Return the result of the function
 RETURN @DT_datetime

 END

and then call it in select like :

declare @next_run_date int, @next_run_time int
select @next_run_date = 20160307
select @next_run_time = 130949

SELECT  @next_run_date inputdate,
                @next_run_time inputtime,
                dbo.udfGetDateTimeFromInteger(@next_run_date, @next_run_time) outputdatetime

Output will be like :

inputdate   inputtime   outputdatetime
20160307    130949      2016-03-07 13:09:49.000

In Numbers on your Mac, change the format of text, numbers, currency, percentages, date and time, and durations in a table. You can format a table cell with a specific data format (for example, number, currency, maximum number of digits you want displayed, or to round the value to the nearest half, quarter, and so on. The following formula will help you converting date/time format cell to date only in Excel. 1. Select a blank cell you will place the date value, then enter formula =MONTH(A2) & "/" & DAY(A2) & "/" & YEAR(A2) into the formula bar and press the Enter key. 2. You can see only the date value is populated in the selected cell based on the date/time cell.

You said those are numbers, right? You can use datetimefromparts (or datetime2fromparts). ie:

select 
 datetimefromparts(
  [date]/10000,
  [date]%10000/100,
  [date]%100,
  [time]/10000,
  [time]%10000/100,
  [time]%100,0)
from tblpress;

DB Fiddle demo

Note that naming fields like that and also storing date and time like that is a bad idea.

I later noticed it was char fields:

select 
  cast([date] as datetime) +
  cast(stuff(stuff([time],5,0,':'),3,0,':') as datetime)
from tblpress;

Note: The TEXT function will convert numbers to text, which may make it Note that Excel rounds the value to 2 decimal places. =TEXT(TODAY(),"MM/DD/YY"). Today's date in MM/DD/YY format, like 03/14/12 Current time, like 1:29 PM. There are many ways in which you can enter time into cell, but Excel has an built-in function which facilitates users to convert values into time format, by using this function you can convert data in hrs:mm format, and hrs:mm:sec format. It automatically shows military time format against the suitable values in the cell.

TIME: -It converts hours, minutes, and seconds given as numbers to an Excel serial number, formatted with a time format. Syntax: =TIME(hour,minute,second). DateNumber = datenum(t) converts the datetime or duration values in the input array t to serial date numbers.. A serial date number represents the whole and fractional number of days from a fixed, preset date (January 0, 0000) in the proleptic ISO calendar.

If you have dates with time values and you want to extract only the date portion, you make sure you use a date format on the result that does not include a time. But INT actually rounds numbers down to the nearest integer, which makes a  To convert a number to a date, you must first convert it to text. Please try the following formula: =DATEVALUE(LEFT(TEXT(D1,"###"),4)&"-"&MID(TEXT(D1,"###"),5,2)&"-"&RIGHT(TEXT(D1,"###"),2)) Then go to Format Cells, choose Number -> Date and set format you need. Hope you’ll find this information helpful.

TEXT. Converts a number into text according to a specified format. TEXT(​number, format). number - The number, date, or time to format. format - The pattern by  This section demonstrates 3 different formulas to convert hours from the standard time format (hh:mm:ss) to a decimal number. Formula 1: Arithmetic calculation. You already know the fastest way to convert a time value to a number of hours in Excel - multiplying by 24, i.e. by the number of hours in one day: =A2*24 (where A2 is the time value)

Comments
  • What database do you use?
  • i have use ms sql server
  • stackoverflow.com/questions/3855867/convert-int-to-datetime-sql docs.microsoft.com/en-us/sql/t-sql/functions/… for the desired output format
  • What's data type in Date and Time? Int?
  • date and time char
  • for the output +1 but thats i think is not a robust way
  • "best" way? Why is this best? Even if creating a function might be good, this is not the function that is best, right?
  • @CetinBasoz Completly agreed I never said that this funcion is the best :).What I meant was to reduce the code overload in the actual place