Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
hi guys
i've a problem with save Date
i want save my date as YYYY/MM/DD
how can i change Save Date Type of YYYY-MM-DD to YYYY/MM/DD as Default
Thanx
Posted 1-Jan-12 23:20pm
sinaone1.3K
Comments
Amir Mahfoozi at 2-Jan-12 10:15am
   
Give an example of your save statement.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Sounds like you're trying to use character data type for dates. Don't do that. Use native data types, either date or datetime. You can format the dates as you like in the UI.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

save normally but retrieving use this syntax to get in respective format
 
SELECT 
REPLACE(CONVERT(varchar(12), Ur_Date, 106), ' ', '-') AS Date
 
FROM Table_Name
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

Read this Thanks ,
 
– Microsoft SQL Server T-SQL date and datetime formats
 
– Date time formats – mssql datetime
 
– MSSQL getdate returns current system date and time in standard internal format
 
SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)
 
                                        – Oct  2 2008 11:01AM
 
SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008
 
SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02
 
SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy
 
SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy
 
SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy
 
SELECT convert(varchar, getdate(), 106) – dd mon yyyy
 
SELECT convert(varchar, getdate(), 107) – mon dd, yyyy
 
SELECT convert(varchar, getdate(), 108) – hh:mm:ss
 
SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)
 
                                        – Oct  2 2008 11:02:44:013AM
 
SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy
 
SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd
 
SELECT convert(varchar, getdate(), 112) – yyyymmdd
 
SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm
 
                                        – 02 Oct 2008 11:02:07:577
 
SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)
 
SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)
 
SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm
 
SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm
 
                                        – 2008-10-02T10:52:47.513
 
– SQL create different date styles with t-sql string functions
 
SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd
 
SELECT convert(varchar(7), getdate(), 126)                 – yyyy-mm
 
SELECT right(convert(varchar, getdate(), 106), 8)          – mon yyyy
 
 
– SQL Server date formatting functionconvert datetime to string
 
————
 
– SQL datetime functions
 
– SQL Server date formats
 
– T-SQL convert dates
 
– Formatting dates sql server
 
CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
 
RETURNS VARCHAR(32)
 
AS
 
BEGIN
 
    DECLARE @StringDate VARCHAR(32)
 
    SET @StringDate = @FormatMask
 
    IF (CHARINDEX (‘YYYY’,@StringDate) > 0)
 
       SET @StringDate = REPLACE(@StringDate, ‘YYYY’,
 
                         DATENAME(YY, @Datetime))
 
    IF (CHARINDEX (‘YY’,@StringDate) > 0)
 
       SET @StringDate = REPLACE(@StringDate, ‘YY’,
 
                         RIGHT(DATENAME(YY, @Datetime),2))
 
    IF (CHARINDEX (‘Month’,@StringDate) > 0)
 
       SET @StringDate = REPLACE(@StringDate, ‘Month’,
 
                         DATENAME(MM, @Datetime))
 
    IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
 
       SET @StringDate = REPLACE(@StringDate, ‘MON’,
 
                         LEFT(UPPER(DATENAME(MM, @Datetime)),3))
 
    IF (CHARINDEX (‘Mon’,@StringDate) > 0)
 
       SET @StringDate = REPLACE(@StringDate, ‘Mon’,
 
                                     LEFT(DATENAME(MM, @Datetime),3))
 
    IF (CHARINDEX (‘MM’,@StringDate) > 0)
 
       SET @StringDate = REPLACE(@StringDate, ‘MM’,
 
                  RIGHT(’0′+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
 
    IF (CHARINDEX (‘M’,@StringDate) > 0)
 
       SET @StringDate = REPLACE(@StringDate, ‘M’,
 
                         CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
 
    IF (CHARINDEX (‘DD’,@StringDate) > 0)
 
       SET @StringDate = REPLACE(@StringDate, ‘DD’,
 
                         RIGHT(’0′+DATENAME(DD, @Datetime),2))
 
    IF (CHARINDEX (‘D’,@StringDate) > 0)
 
       SET @StringDate = REPLACE(@StringDate, ‘D’,
 
                                     DATENAME(DD, @Datetime))
 
RETURN @StringDate
 
END
 
GO
 
 

– Microsoft SQL Server date format function test
 
– MSSQL formatting dates
 
SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’)           – 01/03/2012
 
SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’)           – 03/01/2012
 
SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’)            – 1/03/2012
 
SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’)             – 1/3/2012
 
SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’)               – 1/3/12
 
SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’)             – 01/03/12
 
SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’)         – JAN 03, 2012
 
SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’)         – Jan 03, 2012
 
SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’)       – January 03, 2012
 
SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’)           – 2012/01/03
 
SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’)             – 20120103
 
SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’)           – 2012-01-03
 
– CURRENT_TIMESTAMP returns current system date and time in standard internal format
 
SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,‘YY.MM.DD’)      – 12.01.03
 
GO
 
————
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 756
1 OriginalGriff 452
2 Tadit Dash 350
3 sanket saxena 329
4 Peter Leow 228
0 Sergey Alexandrovich Kryukov 11,890
1 OriginalGriff 7,335
2 Peter Leow 5,003
3 Abhinav S 3,943
4 Maciej Los 3,575


Advertise | Privacy | Mobile
Web01 | 2.8.140421.2 | Last Updated 2 Jan 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid