February 17, 2012 at 2:24 am
how to convert 2012-02-15 14:26:58.520 into 2/15/2012 2:00:00 PM? Please help me with the exact syntax for this. Just highlighting one thing, for any time instance, where the hour is 14 hours and not 15, it has to convert in 2:00:00 PM only. I dont want the minutes.
Regards,
Ganesh.
February 17, 2012 at 2:40 am
CONVERT(datetime, '2006-04-25T15:50:59.997', 131)
SELECT convert(varchar, getdate(), 111
)
http://msdn.microsoft.com/en-us/library/ms187928.aspx
any queries please post !
February 17, 2012 at 3:11 am
Sorry Kshitiz, no luck yet.
February 17, 2012 at 3:20 am
I like this one:
SELECT DATEADD(hour, DATEDIFF(hour, 0, '2012-02-15 14:26:58.520'), 0)
Of course, if you want the PM part, you still need to convert.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 17, 2012 at 3:43 am
SELECT CONVERT(nvarchar(MAX), GETDATE(), 101) + ' ' + Stuff(Right(convert(nvarchar(max),DATEADD(hour, DATEDIFF(hour, 0, GETDATE()), 0),109),13),6,4,'')
Regards
Guru
February 17, 2012 at 4:17 am
Dwain's method is more efficient, since it doesn't involve string conversions. Granted, it doesn't give you the AM/PM, but do you really need that?
If you want proof, run this in a database that has lots of objects in it:
SET STATISTICS TIME ON
SELECT CONVERT(nvarchar(MAX),create_date,101) + ' ' + Stuff(Right(convert(nvarchar(max),DATEADD(hour,DATEDIFF(hour, 0, create_date), 0),109),13),6,4,'')
FROM sys.objects
... and then this:
SET STATISTICS TIME ON
SELECT DATEADD(hour, DATEDIFF(hour, 0, create_date), 0)
FROM sys.objects
John
February 17, 2012 at 5:00 am
Why in SQL? It's not a formatting tool!
You should do it in the UI or report.
If you insist on exact format requested (eg. no leading 0 for month part) your only way is to build the whole string yourself:
declare @dt datetime = '2012-02-15 14:06:08.520'
select cast(month(@dt) as varchar) + '/' +
cast(day(@dt) as varchar) + '/' +
cast(year(@dt) as varchar) + ' ' +
ltrim(reverse(substring(reverse(convert(varchar, @dt, 100)),6,2))) + ':00:00 ' +
right(convert(varchar, @dt, 100),2)
But again, you should do it in UI!!!
February 17, 2012 at 5:27 am
Dwain's reply is very close to requirement, without AM\PM, but I think this is fine for the moment.
Heartly thanks to everyone for your sincere involvement.
February 17, 2012 at 6:01 am
Messy but works
DECLARE @Date DATETIME
DECLARE @tab TABLE (Y VARCHAR(4), M VARCHAR(2), D VARCHAR(2), T VARCHAR(11))
SELECT @Date = DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE() ), 0)
INSERT INTO
SELECT
DATEPART(YEAR,@DATE),
DATEPART(MONTH,@DATE),
DATEPART(DAY,@DATE),
CASE DATEPART(HOUR,@DATE)
WHEN 00 THEN '00:00:00 AM'
WHEN 01 THEN '01:00:00 AM'
WHEN 02 THEN '02:00:00 AM'
WHEN 03 THEN '03:00:00 AM'
WHEN 04 THEN '04:00:00 AM'
WHEN 05 THEN '05:00:00 AM'
WHEN 06 THEN '06:00:00 AM'
WHEN 07 THEN '07:00:00 AM'
WHEN 08 THEN '08:00:00 AM'
WHEN 09 THEN '09:00:00 AM'
WHEN 10 THEN '10:00:00 AM'
WHEN 11 THEN '11:00:00 AM'
WHEN 12 THEN '12:00:00 PM'
WHEN 13 THEN '01:00:00 PM'
WHEN 14 THEN '02:00:00 PM'
WHEN 15 THEN '03:00:00 PM'
WHEN 16 THEN '04:00:00 PM'
WHEN 17 THEN '05:00:00 PM'
WHEN 18 THEN '06:00:00 PM'
WHEN 19 THEN '07:00:00 PM'
WHEN 20 THEN '08:00:00 PM'
WHEN 21 THEN '09:00:00 PM'
WHEN 22 THEN '10:00:00 PM'
WHEN 23 THEN '11:00:00 PM'
END
SELECT
M+'/'+D+'/'+Y+' '+T AS DT
FROM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply