One of the reader Nanda of SQLAuthority.com has posted very detailed script of converting any date time in desired format. I suggest every reader of this blog to save this script in your permanent code bookmark and use it when you need it. Let us learn about User Defined Functions.
Refer the function and get familiar yourself with a different format this function support. I have added a few examples of how this function can be used at the end of the article. You can download the whole code in ZIP format as well.
Download Get Date Time in Any Format Script (ZIP)
I am not including the entire function over here as it can be very long it is very difficult to format. I strongly encourage that you download the function from the URL which I have listed above. I personally find it very helpful and interesting.
SELECT [dbo].[ufsFormat] ('8/7/2008', 'mm/dd/yy') 'Format mm/dd/yy'
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'hh:mm:ss') 'Format hh:mm:ss'
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'mmm') 'Format mmm'
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'Mmm dd yyyy hh:mm:ss:ms AM/PM')
'Format Mmm dd yyyy hh:mm:ss:ms AM/PM'
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', '#') 'Format #'
GO
Let me know what you think of this function. I strongly encourage that you try this out. However, in the latest version of SQL Server, there is a better function called FORMAT which can the date and time in a better format.
Let me know what you think of this new blog article SQL SERVER – Learning New Multipurpose FORMAT Function.
Reference: Pinal Dave (https://blog.sqlauthority.com/), Nanda
51 Comments. Leave new
A very good article for asp.net newbies…Thanks Dave!!
hey,,this needs to be checked out
ELSE 1 END
should be
ELSE -1 END
in line 52
Hi Pinal,
The Above function is not working when i give like the below..
SELECT [dbo].[ufsFormat] (‘8/7/2008’, ‘dd/mm/yy hh:mm:ss:ms AM/PM’)
Hey Boss ur a Genious
Hi Pinal ,
i want to extract time part of date .. but this function is not working …
SELECT [dbo].[ufsFormat] (‘2008-09-02 14:14:00.000’, ‘hh:mm:ss:ms AM/PM’)
giving result as :
09/02/08
Hi,
When i am passing @date input as dd/mm/yyyy’ and wanted to convert into ‘mm/dd/yyyy’. I am getting following error
“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.”.
Could you please let me know what would be the best approach for it when i want to pass date as ‘dd/mm/yyyy’ and wanted the output as ‘mm/dd/yyyy’.
Thanks
Robin
Hi Robin,
It looks like your data might have invalid date.
You can use CONVERT(VARCHAR(100), GETDATE(),101) to display output in mm/dd/yyyy.
Thanks,
Tejas
thanks Pinal for your SQL script. :)
Hi Pinal,
I have a situation in one of my project requirement.
I want date format like ” , “.
Is it possible in sql 2005?
I hope I will find it out asap.Please help me out.
Regards,
Siva
Pinal,
Happy birthday.
I have a table that has an integer field that records time taken as a whole number(don’t ask). For example 1650 = 16:30, or 35 = 00:21. Once the data is summed I want to be able to convert to a hh:mm format.
I have drafted this SQL but get error “Syntax error converting the varchar value ‘0:0’ to a column of data type int.”
DECLARE @SQL_HOLDER nvarchar(3000)
SET @SQL_HOLDER = ‘
declare @hoursall int
declare @hours decimal(18,2)
UPDATE visits
set @hoursall = travel_time,
@hours = @hoursall/100,
travel_time = convert(varchar, convert(int, @hours)) + ”:” + convert(varchar, convert(int, 60 * (@hours – convert(int, @hours)))) ‘
EXEC sp_executesql @SQL_HOLDER
GO
Michael
@MJDA
It might help to see the actual SQL statement that causes the error.
Also, the conversion can be slightly easier than you posted. For minutes, i’d do it slightly differently:
DECLARE @A INT
SET @A = 1650
SELECT CAST(@A / 100 AS VARCHAR(2)) + ‘:’ + CAST (CAST(60 * ((@A % 100) / 100.0) AS INT) AS VARCHAR(2))
i want to store date in sql server 2000 in ‘dd/mm/yyyy’ formate can it is posible please tell me and give me the query how to write it.
@Mayur,
I have written two scripts, Execute them and check the results.
Script 1:
create table #Example1 ( Id int , Date Datetime )
insert into #Example1 values ( 1 , getdate())
select Id, convert (varchar, date, 101) from #Example1
Script 2:
Create table #Example2 ( Id int, Date varchar(11))
insert into #Example2 values ( 1 , convert (varchar(11),getdate(), 101))
select * from #Example2
drop table #Example1 , #Example2
~IM.
@Mayur.
Above format is mm/dd/yy
I forgot you asked, this format : dd/mm/yyyy, to get this format, just replace, 101 by 103 in the above script.
Script 1:
create table #Example1 ( Id int , Date Datetime )
insert into #Example1 values ( 1 , getdate())
select Id, convert (varchar, date, 103) from #Example1
Script 2:
Create table #Example2 ( Id int, Date varchar(11))
insert into #Example2 values ( 1 , convert (varchar(11),getdate(), 103))
select * from #Example2
drop table #Example1 , #Example2
Hi Pinal,
I want following .Net DatTime Format dd/M/yyyy h:mm:ss tt in SQL Server 2005:
I have date in SQL Server is: 2009-11-10 16:20:42.017
and I want result as: 10/11/2009 4:20:42 PM
Regards,
Ankit
Do the formation at .NET
What is the difficulty you are having there?
Hi, Madhivanan
Sorry for delay reply.
Actually I found solution, reference link given by Tejas Shah. thnx Tejas and you also.
heY bOSS U R GENIOUS
Hi Ankit,
Please find attached link for the same:
h ttp://tejasnshah.wordpress.com/2008/12/22/sql-server-get-time-in-ampm-format/
Thanks,
Tejas
hi dave ,
diz babu .i am new to diz blog,i have doubt that,
how to generate the dates in between given dates in sql,
ex :i have date’s 1/11/09 & 2/12/09.if i want to display the dates in between these two .how can i move.
waiting for the reply.plz kindly give me an idea as early as possible.
declare @from datetime ,@to datetime
select @from=’2009-11-01′,@to=’2009-12-02′
select
dateadd(day,number,@from) as dates
from master..spt_values
where type=’p’ and number between 0 and datediff(day,@from,@to)
this is the respnse of the parent form.
Hello pinal dev sir
can we convert current date like following formate
if date is 08/06/2010 then i want to convert like
8th June 2010
if my date is 21/06/2010 then i want
21st June 2010
please tell me
Thanks
Hi my clients need to display date time in following format.
dd/mm/yy hh:mm:ss
Like
29/11/20 11:49:30
Not AM or PM
Day/Months/year(only last two digit) time in 24 Hour format.
How can I do this.
I am using SQL Server 2008 ans asp.net 3.5.
Please help me.
Thanks