Click here to Skip to main content
11,639,178 members (63,879 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server DateTime
how can i show by SELECT QUERY a saved date in sqlserver in dd/mm/yyyy format ???

pls help ..thanks in advance
Posted 7-Mar-13 0:05am
sr_24484

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

DateTime field stores numeric value. So, format is not important, because it's used for visualize data.

To change format of viewed date, use:
SET DATEFORMAT ymd;
SET DATEFORMAT dmy;
SET DATEFORMAT mdy;

More about: DATETIME[^], SET DATEFORMAT[^], CAST and CONVERT[^]

DECLARE @myDate DATETIME
SET @myDate = '2013-03-07'
--USA: mm/dd/yy
SELECT CONVERT(NVARCHAR(30), @myDate, 101) AS [Formated Date]
 
--British/French:dd/mm/yy
SELECT CONVERT(NVARCHAR(30), @myDate, 103) AS [Formated Date]
 
--USA: mm-dd-yy
SELECT CONVERT(NVARCHAR(30), @myDate, 110) AS [Formated Date]
 
--JAPAN: yy/mm/dd
SELECT CONVERT(NVARCHAR(30), @myDate, 111) AS [Formated Date]
  Permalink  
v3
Comments
sr_24 at 7-Mar-13 6:38am
   
u mean i have to alter my table ??????
sr_24 at 7-Mar-13 6:40am
   
I only want to write a Select Query to display date in dd/mm/yyyy format ,though datetype in db is -- DateTime()

what should i do in select query ?
Maciej Los at 7-Mar-13 8:47am
   
No! You don't need to alter/update table.

Please, follow the llinks.
RedDK at 7-Mar-13 12:17pm
   
I'm not a big link follower either ... Try all these:
SELECT CONVERT (time, SYSDATETIME()) AS [SYSDATETIME()]
,CONVERT (time, SYSDATETIMEOFFSET()) AS [SYSDATETIMEOFFSET()]
,CONVERT (time, SYSUTCDATETIME()) AS [SYSUTCDATETIME()]
,CONVERT (time, CURRENT_TIMESTAMP) AS [CURRENT_TIMESTAMP]
,CONVERT (time, GETDATE()) AS [GETDATE()]
,CONVERT (time, GETUTCDATE()) AS [GETUTCDATE()];

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Suvendu Shekhar Giri 280
1 Sergey Alexandrovich Kryukov 279
2 Richard Deeming 200
3 CPallini 187
4 PIEBALDconsult 175
0 OriginalGriff 9,081
1 Sergey Alexandrovich Kryukov 8,812
2 Mika Wendelius 7,027
3 Suvendu Shekhar Giri 2,600
4 F-ES Sitecore 2,548


Advertise | Privacy | Mobile
Web03 | 2.8.150728.1 | Last Updated 7 Mar 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100