Date and Time Conversions Using SQL Server (2024)

You could just do UPDATE statements to convert your text values to the standard time values you mentioned. Another option is to use a CASE statement to make the change if you don't want to change the actual data.

https://www.mssqltips.com/sqlservertip/6031/sql-server-case-expression-overview/

-Greg

Please help me with this..

I got a really messy data that I am trying to clean. There is a column there that has time. Most of the time values are entered in this format 18h00, which is fine. But there are rows that have “morning” or “afternoon” or “after dark” instead of actual time.

My question is: how do I convert these strings like “afternoon” which I know could be like “13h00” or something to a time value instead of having it as a string.

(my parametr type as datetime)

please help me

try this.

declare @d int = 44542 -- Dec 14, 2021
select CONVERT(varchar(25),cast(@d as datetime),105)

-Greg

I want to convert a Text Field which is saving the Data in String into a Date field. The value stored is in the below format.

Value Stored = 66028
Actual Value (or) Desired Output = 10-11-2021 (dd-mm-yyyy)

Thanks,
Krishna

you could create another table with the mapping and then join to that table based on the day of the month to return your A, B, C, etc. values.

-Greg

could we do like code this sql please suggest solution if it could be done.

Thanks

Greg

Be advised that the FORMAT function is horrible for performance. It takes a very typical 43 times longer than even some of the more complex things you can do with CONVERT. I STRONGLY RECOMMEND AVOIDING THE USE OF THE FORMAT FUNCTION FOR THAT REASON. It's the "death of a thousand small cuts".

Hi George, you can do this with the format function as well. Here is a tip about that:https://www.mssqltips.com/sqlservertip/2655/new-sql-server-function-to-format-dates/

Also, I have heard that the format function is a lot slower than the convert function.

-Greg

Wouldn't most of this be moot with the FORMAT function? That will automatically turn it into an NVARCHAR(4000) field, and you have a lot of flexibility to define it how you want.

select
format(getdate(), 'yyyy-MM-dd hh:mm:ss') AS [today],
format(getdate(), 'yyyy-MM-dd') AS [today2]

THANK YOU!

Thanks Gregg for the feedback. We will see if we can update all of the examples.

The reader could also run the T-SQL script and enter other dates. This will produce all valid results, so they can see how the date would be formatted.

Thanks
Greg

revise your example date, please. use a example date (ex, 1/2/1994) that has a month AND day less than 10 so readers can see if the leading zero is prefixed or not.

THANK YOU

Thank for the providing that giant table formats!

Thank you for the good tip.

I also see sometimes the column could store seconds only. The seconds is the seconds since 0 hours of the day.

Any conversion formula?

Thanks

Thanks Stewart for the input.

As you mentioned this tip uses VARCHAR and that could be changed to NVARCHAR which will return the same results for most of the items and take care of format 130.

-Greg

Formats 130 and 131 are the Islamic calendar date. But 130 contains Arabic characters, and therefore you would need to convert to nvarchar, not varchar. Still, the order of the parts comes out in a bit of a muddle, unless I tell my software to render it right-to-left.

Hi Ben,

you can do this:

SELECT CONVERT(datetime,'1/1/2018 12:00:00 AM')

-Greg

Does anyone know how to convertthis format "1/1/2018 12:00:00 AM" (currently in text) to a date time type?

Thanks,

Ben

Its very nice article .

Its saved my time.

Thank you.

Hi Zulfiqar, it looks like the output is 2019-03-20.

You could also just do this and get the same result. The third parameter doesn't seem to make a difference.

SELECT CONVERT(DATE, GETDATE())

-Greg

Hi What would this output?

CONVERT(DATE, GETDATE(), 123)

Thank you.

i love you guys

Hi David,

Take a look at this tip:

https://www.mssqltips.com/sqlservertip/2655/format-sql-server-dates-with-format-function/

-Greg

Hello,

Thank you for this great post,but i can't find this date format

Jeu. 07 Mar 2019

any suggestion please ?

Thank you

A simpler way to format the date is:

SELECT CAST(GETDATE() AS DATE) AS ‘ColumnName’

This will produce a date in this format: 2018-03-22

You can replace the “GETDATE()” with the name of date column you want to modify.

As for the Time, use:

SELECT FORMAT(GETDATE(), ‘hh:mm’) AS ‘ColumnName’

This will produce the time in this format: 01:07

Or, if you want the time returned in Military format, change the ‘hh’ to upper case ‘HH’

SELECT FORMAT(GETDATE(), ‘HH:mm’) AS ‘Time’

This will produce the time in this format: 13:09

Hope this helps someone.

This was helpful

Excellent Post

I would add that cast(date_expression as date) is sometimes a usefulsolution. It keeps the date characteristics for sorting, comparing, etc. but drops the time portion. This is great for items going to excel.

Hi Koduru,

Take a look at this tip: https://www.mssqltips.com/sqlservertip/2507/determine-sql-server-date-and-time-with-datepart-and-datename-functions/

hi how to get result in sql server like

year:2017

date:06

month:04

please help me

thank u.

01/01/0100 10:00:00 how to convert time 10:00:0

hello,i just want to create a date from the table in sql server 2008.But there one error was araised whatever change the datatype(varchar,int,char).such as that error is,

"Arithmetic overflow error converting expression to data type int.
The statement has been terminated".

how ll clear this error.

Hi there,

I am retrieving data with SQL from a Oracle database where I encounter different date formats in the same data group.

I run the SQL query in VBA and the query results are pasted in an excel tab. So in the same data column I have data with multiple date formats in excel.

Only one date format recognized as date, according to pc's regional settings. I need to convert all data into same format before I retrieve them to excel.

right now I use this:TO_CHAR(v.BASLANGICTARIHI, 'DD/MM/YYYY') but it does not help.

Can someone help me here?

Thanks.

--Output as char with space like 3 1 1 0 2 0 1 4.
declare @dt varchar(120)
declare @hold1 varchar(2)
declare @hold2 varchar(15)
declare @i int

set @dt = CONVERT(varchar(26),getdate(),103)
set @dt = REPLACE(@dt,'/','')
set @i=1
set @hold2=''

WHILE (@i < len(@dt)+1)
BEGIN
set @hold1 = substring(@dt,@i, 1)+' '
set @hold2 += @hold1
set @i += 1
END
select @hold2

HiGreg Robidoux,

Thanks a lot.

Your suggestion were working well... Really you have given me a great thing.

Once again thanks....

Hi San,

use this select replace(convert(varchar, getdate(),103),'/','') to get the output you need and then use one of these functions to add the space between each number

http://dbamentalist.wordpress.com/2013/06/17/t-sql-function-add-a-space-between-all-characters/comment-page-1/

http://www.codeproject.com/Tips/426728/T-SQL-Function-Add-a-space-between-all-characters

Input date (31/10/2014)

Output as char with space like 3 1 1 0 2 0 1 4.

Can anyone help me?

This is for cheque date printing...

satheeshkumar,

Have you checked out Tim Ford's tip - http://www.mssqltips.com/sqlservertip/1712/sql-server-function-to-convert-integer-date-to-datetime-format/?

I believe he has a function to take care of the date logic, but I think you will have to modify it to include the time logic you need.

HTH.

Thank you,
Jeremy Kadlec

hI

BELOW MY REQUORMENT

Input - 20121130121020 = output 2012/11/30 12:10:20

Can any one help me out this

Dev,

Can you post the date format you are seeing in SSMS and the final format you would like?

Thank you,
Jeremy Kadlec

How to convert datetime format stored data to 24hr time format in SSMS 2008?
Any idea on this?

Don't forget 23: 2006-12-30

i want extracting date from sql server,using php how to get this?? i used

$d=convert(varchar,getdate(),101);

echo $d;

this is not working

its very very helpful...

still i need few more details... for few more formulaes....

Tessa,

Would this work for you?

convert(varchar,getdate(),105) + ' ' + convert(varchar,getdate(),108)

Is there any reason you would not use this format:

Have you also seen these tips:

http://www.mssqltips.com/sqlservertip/2588/new-date-and-time-functions-in-sql-server-2012/

http://www.mssqltips.com/sqlservertip/1616/sql-server-2008-date-and-time-data-types/

HTH.

Thank you,
Jeremy Kadlec

I would like to see the complete date and time a DD-MM-YYYY HH:MM:SS

I use:convert(varchar,getdate(),105) + convert(varchar,getdate(),108)

I get: 16-05-201213:42:18

How would I get the extra space between the Date and Time to get 16-05-2012 13:42:18

It's very helpful info thanks for help!!!!!

Actually, I was simply complimenting the "tip" as it provided exactly what I needed for ANY date:

select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')

This converts any MSSQL datetime to MMDDYYYYHHMMSS - exactly what the Oracle folks wanted!

Thanks anyway :-)

Select the particular year,month, day,pls do following query

---------------------------------------------------------------------------------------

select * from barrowBooks where year(barrow_date)='2008' and month(barrow_date)='11' and day(barrow_date)='12' order by barrow_date desc

convert the date and time using this query

----------------------------------------------------------------

select convert(char(11),getdate(),108) -->Result of time 11:18:18

select convert(char(10),getdate(),101) -->Result of Date 11/06/2008

I just had a request from our Oracle team to see if I could supply them with dates in a MMDDYYYYHHMMSS format. Came here and found what I need in all of about 30 seconds. Thanks.