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
Hi All ,
Can any one answer how to get a series of dates with in a particular starting and ending date ?
For example my starting is ’01/18/2011′ and my ending date is ’01/25/2011′, i need to get all the dates between those dates like,
Output:
01/18/2011
01/19/2011
01/20/2011
01/21/2011
01/22/2011
01/23/2011
01/24/2011
select dateadd(day,number,cast(‘20110118’ as datetime)) from master..spt_values
where dateadd(day,number,cast(‘20110118’ as datetime))<'20110125' and type='p'
Thanks for your reply Madhivanan,
The following code also will return the same result,
Here i have used CTE,
please check the code.
declare @varstartdate datetime
declare @varenddate datetime
declare @vardiff int
set @varstartdate = ’01/18/2011′
set @varenddate = ’01/25/2011′
set @vardiff = DATEDIFF(d,@varstartdate,@varenddate)
declare @s varchar(max)
set @s=’with numbers as
(
select 1 as Num, convert(datetime,”’ + convert(varchar,@varstartdate,101) + ”’) as vardate1
union all
select Num + 1, convert(datetime,”’ + convert(varchar,@varstartdate,101) + ”’) + Num from numbers where Num < ' + convert(varchar,@vardiff) + '
)
select convert(varchar,vardate1,101) from numbers option(Maxrecursion 25)'
execute (@s)
CONVERT(CHAR(24), @dtGetDate, 103), converts format of @dtGetDate to 103.
Can there be a way to know in which format @dtGetDate is?
What is the datatype of @dtGetDate?
need a format DD/MM/YYYY HH:MI:SS how could i perform it
how to display in DD – MMM – yyyy where dash are compulsory . Not DD MMM YYYY
Hi,
I have date format like this
select [dbo].[ufsFormat]
(’09-Jan-2012 3:15:58 PM’, ‘dd-Mmm-YYYY hh:MM:SS AM/PM’) and i am getting the result 09-Jan-2012 3:15:58 PM.
But i need output is like(have to add zero in my hours when it is in 12 hours format) 09-Jan-2012 03:15:58 PM.
I am using Sqlserver2005.
Can any one help me out.
hi
i need a style format value for MM/dd/yyyy hh:mm:ss AM
For eg like this 02/05/2012 12:20:23 AM
Nithin
hi
i need a style format value for MM/dd/yyyy hh:mm:ss AM
For eg like this 02/05/2012 12:20:23 AM
I am using Sql server 2008 R2
Nithin
Have a look at CONVERT function in SQL Server help file
I have one column in a varchar format which represent year and month in ‘YYYYMM’ fromat. How can I extract the year and month from it i.e. if the data is ‘201104’, I want it in the form of ‘2011 APR’?
Can anyone help me out?
SELECT CONVERT(varchar, CONVERT(datetime, ‘2014/12/30’), 100)
Hi,
is it possible to convert Jan 25,2012 to 2012-01-25? pls tell possible solution. thanks
How can i get 12 Hour Time in following format
5:20 PM
I have two columns “InTime” & “OutTime” both having type DateTime
I want to show the InTime and OutTime in following format
Hour:Minute AM/PM
also that i want the difference of OutTime and InTime In Hours and Minutes separately.
Help me in such regards
Thanks
How can I change ‘Fri Jan 11 00:00:00 EST 2013’ into datetime format
Does it work on sql server 2008 R2?
I am passing @week =First week @Day=Monday,@month=’MAY’,@Year=2014 in result = date like 05/05/2014
Thank You Dave!!! Sergio Aranda
Hi sir,i have one doubt , is there any patch file for sqlserver ,means i want sqlserver but its size is min 4GB ,but i want use only tables and view and procedures only these are my requirement ,so i dont want install 4GB file ,i am looking for small patch or small size sqlserver file ,it would be work for tables ,views ,procedures …please give me that file if any
Thanks in Advance
srinivas
Hi sir,i have one doubt , is there any patch file for sqlserver ,means i want sqlserver but its size is min 4GB ,but i want use only tables and view and procedures only these are my requirement ,so i dont want install 4GB file ,i am looking for small patch or small size sqlserver file ,it would be work for tables ,views ,procedures …please give me that file if any
please mail me : [email removed]
Thanks in Advance
srinivas
How to print the date as char like 3 1 1 0 2 0 1 4…
Kindly help me.
Deal Pinal,
I have written once report procedure to print the data in bank cheque like Name, Amount and Amount In Words.. In that I need to print the date (31/10/2014) into char like 3 1 1 0 2 0 1 4….
Kindly help me out ASAP….
Hi,
Can i get the date displayed in dd*mm*yyyy format? If yes, How??