November 10, 2009 at 11:52 pm
Hi All,
Is there a direct way to get a date value (say GETDATE()) to this format "DD-MON-YYYY HH:MM AM"?
For Example:
Input Date Value = 2009-01-01 01:00:00.00 (assume it is AM)
Output required = ''01-Jan-2009 1:00 AM"
Input Date Value = 2009-01-01 01:00:00.00 (assume it is PM)
Output required = ''01-Jan-2009 1:00 PM"
I created this query :
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') +' '+STUFF(RIGHT( CONVERT(VARCHAR,GETDATE(),100 ) ,7), 6, 0, ' ')
This works pretty fine. But it involves lot of convert, cast, replace etc etc.. I want a simple way to get the output required. Is there any Styles in CONVERT function that I might use? or any in-built funtion in SSMS to do this?
I use SQL Server 2005.
Any help will be much appreciated!
Thanks in Advance!
November 11, 2009 at 1:45 am
Does this code help you?
declare @MyDate datetime;
set @MyDate = getdate();
select convert(varchar(30), @MyDate, 113);
select case when datepart(hh, @MyDate) > 12 then replace(convert(varchar(30), @MyDate, 113), cast(datepart(hh, @MyDate) as varchar), cast((datepart(hh, @MyDate) - 12) as varchar)) + ' PM'
else convert(varchar(30), @MyDate, 113) + ' AM' end;
set @MyDate = dateadd(hh, -3, getdate());
select convert(varchar(30), @MyDate, 113);
select case when datepart(hh, @MyDate) > 12 then replace(convert(varchar(30), @MyDate, 113), cast(datepart(hh, @MyDate) as varchar), cast((datepart(hh, @MyDate) - 12) as varchar)) + ' PM'
else convert(varchar(30), @MyDate, 113) + ' AM' end;
If you can use the 24 time, then you don't need the replace and concatentation code.
November 11, 2009 at 1:48 am
SELECT dbo.FormatDateTime(CURRENT_TIMESTAMP, N'dd-MMM-yyyy hh:mm tt')
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 11, 2009 at 1:54 am
Of course, I cheated slightly - it depends on the CLR function below: 😀
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;
public partial class UserDefinedFunctions
{
[SqlFunction
(
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = true,
IsPrecise = true
)
]
public static SqlString FormatDateTime
(
[SqlFacet(IsNullable = false)] SqlDateTime dateTime,
[SqlFacet(IsNullable = false, MaxSize = 30)] SqlString format
)
{
try
{
return (SqlString)dateTime.Value.ToString(format.Value, DateTimeFormatInfo.InvariantInfo);
}
catch (System.Exception)
{
return SqlString.Null;
}
}
};
It is very useful - any date/time format you like, without all that tedious messing about.
Of course, you could write something similar in T-SQL and wrap it in a function too - but it's not really the right tool for the job. The .NET routine can do it with just the one line really:
return (SqlString)dateTime.Value.ToString(format.Value, DateTimeFormatInfo.InvariantInfo);
I just jazzed it up a bit 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 11, 2009 at 2:20 am
Lynn Pettis (11/11/2009)
Does this code help you?
declare @MyDate datetime;
set @MyDate = getdate();
select convert(varchar(30), @MyDate, 113);
select case when datepart(hh, @MyDate) > 12 then replace(convert(varchar(30), @MyDate, 113), cast(datepart(hh, @MyDate) as varchar), cast((datepart(hh, @MyDate) - 12) as varchar)) + ' PM'
else convert(varchar(30), @MyDate, 113) + ' AM' end;
set @MyDate = dateadd(hh, -3, getdate());
select convert(varchar(30), @MyDate, 113);
select case when datepart(hh, @MyDate) > 12 then replace(convert(varchar(30), @MyDate, 113), cast(datepart(hh, @MyDate) as varchar), cast((datepart(hh, @MyDate) - 12) as varchar)) + ' PM'
else convert(varchar(30), @MyDate, 113) + ' AM' end;
If you can use the 24 time, then you don't need the replace and concatentation code.
Hi Lynn, thanks for the reply. But what I want is to remove the milliseconds from the date. Your code is giving out milliseconds also :ermm: I am actually pulling the dates from a column through a cursor, hence i need a compact code.
Paul White (11/11/2009)
SELECT dbo.FormatDateTime(CURRENT_TIMESTAMP, N'dd-MMM-yyyy hh:mm tt')
Thanks Paul,but i should not use CLR Procs nor i am allowed to create functions. I need simple T-SQL query to get it done
WHat about my query, do you people think it wont work?
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') +' '+STUFF(RIGHT( CONVERT(VARCHAR,GETDATE(),100 ) ,7), 6, 0, ' ')
i checked it and it is working fine!!
November 11, 2009 at 3:39 am
COldCoffee (11/11/2009)
Thanks Paul,but i should not use CLR Procs nor i am allowed to create functions. I need simple T-SQL query to get it done
Thanks for mentioning that up front.
COldCoffee (11/11/2009)
WHat about my query, do you people think it wont work?
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') +' '+STUFF(RIGHT( CONVERT(VARCHAR,GETDATE(),100 ) ,7), 6, 0, ' ')i checked it and it is working fine!!
Good for you.
It has a double space if the hour part of the time is a single digit, and the VARCHAR(11) should be a CHAR(11) but otherwise it seems fine.
I am resisting the urge to ask why you are doing this, why you think you need a cursor, why it needs to be 'compact' (number of characters?)...and so on.
I am assuming it is some kind of assignment or something.
edit: fixed a quote tag
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 11, 2009 at 9:58 pm
It has a double space if the hour part of the time is a single digit, and the VARCHAR(11) should be a CHAR(11) but otherwise it seems fine.
Thanks for the comments Paul, am working on implementing them!;-)
I am resisting the urge to ask why you are doing this, why you think you need a cursor, why it needs to be 'compact' (number of characters?)...and so on.
I am assuming it is some kind of assignment or something.
I have a customer where i must send each users of their application, the date when their requests were processed. This will be a fairly smaller table, hence CURSOR dint hurt me. As for the DATE, that is the mandated format we must follow irrespective of the customer.
Why it should be compact is for the readability! Already i have created 5 SPs, hence i dint want to add one more funtion to get this DATE alone.
Cheers!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply