CODEWITHSUNDEEP
sql-server
madphp
madphp

Reputation: 1764

SQL Server Convert DD/MM/YY to YYYY-DD-MM

I'd like to do this in the INSERT SQL. Can anyone show me.

Thanks

--Mark

Upvotes: 0

Views: 17270

Answers (5)

Sarath Subramanian
Sarath Subramanian

Reputation: 21271

We doesn't know whether YY in DD/MM/YY is in current century or previous century. For example, if YY is 15, we doesn't know whether that is 1915 or 2015. So the numbers between 0 and 15 will be treated as current century. If the current year is 2016, then the numbers between 0 and 16 will be treated as current century(its dynamic).

EXAMPLE

  • When YY is 15, then YYYY will be 2015.
  • When YY is 97, then YYYY will be 1997.

QUERY

DECLARE @DATECOL VARCHAR(13) = '25/01/15'


SELECT CASE WHEN RIGHT(@DATECOL,2) BETWEEN 0 AND CAST(RIGHT(YEAR(GETDATE()),2)AS INT) 
THEN '20' + RIGHT(@DATECOL,2) ELSE '19' + RIGHT(@DATECOL,2) END
+ '-' + LEFT(@DATECOL,2) + '-' +  LEFT(RIGHT(@DATECOL,5),2)

Upvotes: 0

Fredou
Fredou

Reputation: 20090

complete list of date format, or close

Upvotes: 3

pjp
pjp

Reputation: 17629

Take a look at this custom date formatting function from http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

This allows you to format dates in anyway you want. However i'm not sure why you'd want a date in the format YYYY-DD-MM as this is rather non-standard.

————
– 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

Then you can call this: SELECT dbo.fnFormatDate (getdate(), 'YYYY-DD-MM')

Upvotes: 0

butterchicken
butterchicken

Reputation: 13883

I'm not sure that I follow entirely, but if you are taking a datetime of the form 'dd/mm/yy' and are trying to get it into a varchar(10) of the form yyyy-dd-mm then I think you'll need to convert to a varchar and then use SUBSTRING, LEFT and RIGHT to pull out the data you need:

declare @dt datetime 
select @dt = getdate() -- i.e. 04/09/2009

declare @str varchar(8)
select @str = convert(varchar(8),@dt,112)  --gives 20090904
select LEFT(@str,4) + '-' + RIGHT(@str,2) + '-' + SUBSTRING(@str,5,2) -- gives 2009-04-09

It isn't pretty, but it works!

Upvotes: 1

John Sansom
John Sansom

Reputation: 41819

Something like this should work:

insert into TableName(DateColumn)
select convert(datetime,getDate(),103)

Upvotes: 2

Related Questions