March 4, 2013 at 11:52 am
Hi friends,
Im new to TSql -
I have requirement to display date as 7/31/2013 but currently I'm getting it from DB as 2013-03-01 18:40:24.000.
any help on this will he highly appreciated,
Thanks
Dhananjay
March 4, 2013 at 12:05 pm
dhananjay.nagarkar (3/4/2013)
Hi friends,Im new to TSql -
I have requirement to display date as 7/31/2013 but currently I'm getting it from DB as 2013-03-01 18:40:24.000.
any help on this will he highly appreciated,
Thanks
Dhananjay
If at all possible formatting should be handled in the front end instead of sql. However if you MUST use sql you will need to use CONVERT for this type of thing.
http://msdn.microsoft.com/en-us/library/ms187928.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 4, 2013 at 1:37 pm
Thanks so much that worked as per requirement 🙂
March 4, 2013 at 3:57 pm
dhananjay.nagarkar (3/4/2013)
Thanks so much that worked as per requirement 🙂
Just make sure you don't actually store formatted dates. It WILL come back to bite you in a major way!
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2013 at 10:23 pm
Here's another option for formatting dates. I didn't create the logic behind this function other than to turn it into an iTVF rather than its original scalar form. I don't remember who wrote the original code but kudos to the anonymous developer.
A table of random dates to format (with a little assist from Jeff Moden):
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[RandomDate] DATETIME NULL,
PRIMARY KEY (ID))
INSERT INTO #TempTable
SELECT RandomDate
FROM (
SELECT TOP 100
RandomDate = DATEADD(second,ABS(CHECKSUM(NEWID()))%36000, DATEADD(day,ABS(CHECKSUM(NEWID()))%3653+36524,'2000-01-01 00:00:01'))
FROM Master.dbo.SysColumns t1
)d
The function:
CREATE FUNCTION dbo.itvfFormatDateWithMask
(
@date AS DATETIME
,@format_string VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
/*
SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'YYYY-MM-DD HH:mm:ss.fff')
SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'MMMM Dx, YYYY hh:mmtt')
'YYYY' - full year with century
'YY' - year without century
'Y' - last digit of year
'MMMM' - month name
'MM' - month number with leading zero
'M' - month number without leading zero)
'DDDD' - day name
'DD' - day number with leading zero
'D' - day number without leading zero
'HH' - hour with leading zero (24 hr format)
'H' - hour without leading zero
'hh' - hour with leading zero (12 hr format)
'h' - hour without leading zero (12 hr format)
'mm' - minutes with leading zero
'm' - minutes without leading zero
'ss' - seconds with leading zero
's' - seconds without leading zero
'fff' - milliseconds with leading zeros
'f' - milliseconds without leading zeros
'tt' - AM or PM
't' - A or P
'x' - st, nd, or th
*/
WITH fourRows(N)
AS (
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
)
,cteTally(N)
AS (
SELECT TOP (50)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
FROM
fourRows AS A
CROSS JOIN fourRows AS B
CROSS JOIN fourRows AS C
ORDER BY 1
)
,tokenizedString
AS (
SELECT
N
,REPLACE(C,SUBSTRING(CAST(C AS NVARCHAR(50)),CHARINDEX('""',CAST(C AS NVARCHAR(50)),1),CHARINDEX('""',CAST(C AS NVARCHAR(50)),2)),'ZZZZZZZ') AS C
,groupId = DENSE_RANK() OVER (ORDER BY C, _groupId)
FROM
(
SELECT
N
,SUBSTRING(@format_string COLLATE Latin1_General_CS_AS,N,1) AS C
,_groupId = N - ROW_NUMBER() OVER (PARTITION BY SUBSTRING(@format_string COLLATE Latin1_General_CS_AS,N,1) ORDER BY N)
FROM
cteTally
WHERE
N <= LEN(@format_string)
) AS fs)
SELECT
(SELECT
CASE REPLICATE(MIN(C),COUNT(*))
WHEN 'YYYY' THEN RIGHT('0000' + CAST(YEAR(@date) AS NVARCHAR(4)),4)
WHEN 'YY' THEN RIGHT('00' + CAST(YEAR(@date) AS NVARCHAR(4)),2)
WHEN 'Y' THEN CAST(CAST(RIGHT('00' + CAST(YEAR(@date) AS NVARCHAR(4)),1) AS INT) AS NVARCHAR(2))
WHEN 'MMMM' THEN DATENAME(month,@date)
WHEN 'MM' THEN RIGHT('00' + CAST(MONTH(@date) AS NVARCHAR(2)),2)
WHEN 'M' THEN CAST(MONTH(@date) AS NVARCHAR(2))
WHEN 'DDDD' THEN DATENAME(weekday,@date)
WHEN 'DD' THEN RIGHT('00' + CAST(DAY(@date) AS NVARCHAR(2)),2)
WHEN 'D' THEN CAST(DAY(@date) AS NVARCHAR(2))
WHEN 'HH' THEN RIGHT('00' + CAST(DATEPART(hour,@date) AS NVARCHAR(2)),2)
WHEN 'H' THEN CAST(DATEPART(hour,@date) AS NVARCHAR(2))
WHEN 'hh' THEN RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),2)
WHEN 'h' THEN
CASE WHEN LEFT(
RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),2),1) = 0 THEN
RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),1)
ELSE
RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),2)
END
WHEN 'mm' THEN RIGHT('00' + CAST(DATEPART(minute,@date) AS NVARCHAR(2)),2)
WHEN 'm' THEN CAST(DATEPART(minute,@date) AS NVARCHAR(2))
WHEN 'ss' THEN RIGHT('00' + CAST(DATEPART(second,@date) AS NVARCHAR(2)),2)
WHEN 's' THEN CAST(DATEPART(second,@date) AS NVARCHAR(2))
WHEN 'fff' THEN RIGHT('000' + CAST(DATEPART(millisecond,@date) AS NVARCHAR(3)),3)
WHEN 'f' THEN CAST(DATEPART(millisecond,@date) AS NVARCHAR(3))
WHEN 'tt' THEN
CASE
WHEN DATEPART(hour,@date) >= 12 THEN N'PM'
ELSE N'AM'
END
WHEN 't' THEN
CASE
WHEN DATEPART(hour,@date) >= 12 THEN N'P'
ELSE N'A'
END
WHEN 'x' THEN
CASE
WHEN CAST(DAY(@date) AS INT) IN (1,21,31) THEN N'st'
WHEN CAST(DAY(@date) AS INT) IN (3,23) THEN N'rd'
ELSE N'th'
END
ELSE
MIN(C)
END
FROM
tokenizedString
GROUP BY
groupId
ORDER BY
MIN(N)
FOR
XML PATH('')
,TYPE
).value('(./text())[1]','nvarchar(50)')
AS FormattedDate
)
An example of usage:
SELECT *
FROM #TempTable AS tt
CROSS APPLY
dbo.itvfFormatDateWithMask(tt.RandomDate,'MMMM Dx, YYYY h:mtt') AS d1
CROSS APPLY
dbo.itvfFormatDateWithMask(tt.RandomDate,'MM-DD-YYYY HH:mm') AS d2
March 15, 2013 at 1:46 am
your simple way of doing this is :-
select cast(month(GETDATE()) as varchar(2))+'/'+cast(day(GETDATE()) as varchar(2))+'/'+cast(year(GETDATE()) as varchar(4))
March 15, 2013 at 2:31 am
s.lakhanpal6776 (3/15/2013)
your simple way of doing this is :-select cast(month(GETDATE()) as varchar(2))+'/'+cast(day(GETDATE()) as varchar(2))+'/'+cast(year(GETDATE()) as varchar(4))
Using CONVERT with correct format code is the simplest. This is a lot of work.
March 15, 2013 at 3:13 am
Thanks Lakhan.
It realy helped me .
Kind Regards
Dhananjay
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply