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

Question

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

Thanks

--Mark

Answer 1

Something like this should work:

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

Answer 2

complete list of date format, or close

Answer 3

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!

Answer 4

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')