How to add a left 0 to a datetime

Asked

Viewed 171 times

1

I need to be able to add a 0 to the left in the hours when sql only takes 3 digits or it is from midnight to 9 am so that the information is ordered!

Because if not the information I have is in the following order 0:00 1:00 10:00 11:00 ..... 19:00 2:00 20:00....

And what I want is for the hours to be in order... My idea would be to turn the datetime into varchar to increase the 0 and only then the correct time, but I can not find tools to do it... Can someone help me?

select [Hora] 

case when len(cast([Hora] as varchar(4))) = 4 then '0' + cast([Hora] as varchar(2))


from [dbo].['Data16_Agosto-2016$']

(I’m sorry but I still don’t know how to put the code with the format code here in the overflow)

sql sql-server datetime

  • Edit the question with your query that will help you.

  • @arllondias already put the code

  • check in the language manual that you are using the LPAD function, it fills the fields on the left with the remaining fields you need, example LPAD([hour],2,0) It will complete with 0 until you reach 2 houses, if you already have 2 houses does nothing

  • @Ricarte How the column is declared Hora? What is the database manager? (mariaDB, Oracle Database, SQL Server etc.)

  • @Josédiz SQL server

  • @Josédiz SQL Sever.. The hour column is declared as datetime

  • @Ricarte The column Hora is declared as datetime; ok. But what contains the table Data16_Agosto-2016$? All the rows in this table are of the same date, as the name of the table suggests? It is to list the date and time or only the time?

  • @Josédiz the table contains lots of data, the hour column only determines the time, I have another column 'Date' with the respective date! There are random records per hour, ie (ex) the range from 9:00 to 10:00 there may be 4 or 10, until there may not even be records at that time, it is random. but then, to group the information by, the hours are not sorted because after the 1:00 info it follows 11:00, 12:00 as if it were sorted alphabetically, (19:00, 2:00, 20:00) it is clear now?

Show 3 more comments

1 answer

0

Use the format method();

Example:

select format(cast([NOME_DA_COLUNA] as DATETIME), "hh:mm") 
from [dbo].[NOME_DA_TABELA]

  • And if the time is 13?

  • Ah, I get it now: select [Time] case when Len(cast([Time] as varchar(4))) = 4 then format(cast([Time] as DATETIME), "hh:mm") from [dbo]. ['Date 16_August-2016$']

  • @flaubert165 can then put a correct solution sff?

  • @Ricarte, have you tested the above solution? I’ve already edited.

  • I had not seen that I had edited @flaubert165

  • @flaubert165 has already worked. Thank you

  • update [dbo]. ['Data16_agosto-2016$'] set Hora = format(cast([Hora] as DATETIME), 'hh:mm')

  • I tried to do this anyway but it did not result to update the data, the order of the data appears to me in the same order that I was before doing this update, what I must do for the order to be 0,1,2,3,4,5,6,7,8,9,10,11.... and not 0,1,11,12,2,20,21,22,3,4,5,6,7...? @flaubert165

  • It’s because your column is varchar, so select works. But if you use this same script to update goes from the error, because it returns a DATETIME and its column is VARCHAR. You have to cast() dnvo to scan. Example: update [dbo]. ['Data16_agosto-2016$'] set Hora = cast(format(cast([Hora] as DATETIME), 'hh:mm') as varchar(2) )

Show 4 more comments

Browser other questions tagged sql sql-server datetime

You are not signed in. Login or sign up in order to post.