Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

date format 7/31/2013 Expand / Collapse
Author
Message
Posted Monday, March 4, 2013 11:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181

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
Post #1426385
Posted Monday, March 4, 2013 12:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 13,069, Visits: 11,908
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1426396
Posted Monday, March 4, 2013 12:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:55 AM
Points: 49, Visits: 567
declare @dt datetime
set @dt = '2013-03-01 18:40:24.000'
select convert(varchar(10), @dt, 101 )
Post #1426402
Posted Monday, March 4, 2013 1:37 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
Thanks so much that worked as per requirement :)
Post #1426444
Posted Monday, March 4, 2013 3:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1426501
Posted Monday, March 4, 2013 10:23 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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



 
Post #1426598
Posted Friday, March 15, 2013 1:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 16, 2013 3:13 AM
Points: 4, Visits: 37
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))
Post #1431389
Posted Friday, March 15, 2013 2:31 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:50 PM
Points: 23,003, Visits: 31,495
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1431404
Posted Friday, March 15, 2013 3:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 1:38 PM
Points: 90, Visits: 181
Thanks Lakhan.
It realy helped me .

Kind Regards
Dhananjay
Post #1431413
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse