SQL Server datetime formatting

Which SQL Server of version are you using?

This will work on SQL Server 2012 and above

SELECT FORMAT(GETDATE() ,'yyyyMMdd')

OUTPUT

/*------------------------
SELECT FORMAT(GETDATE() ,'yyyyMMdd')
------------------------*/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20171107

(1 row(s) affected)

Select allOpen in new window

edited my last comment.

This will work in all the Versions of SQL Server .

SELECT REPLACE(LEFT(CONVERT(VARCHAR, GETDATE(), 20), 10),'-','') FormattedDate

OUTPUT

/*------------------------
SELECT REPLACE(LEFT(CONVERT(VARCHAR, GETDATE(), 20), 10),'-','') FormattedDate
------------------------*/
FormattedDate
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20171107

(1 row(s) affected)

Select allOpen in new window

and if you are using SQL 2012 + then you can use below -

SELECT FORMAT(GETDATE() ,'yyyyMMdd')

/*------------------------
SELECT FORMAT(GETDATE() ,'yyyyMMdd')
------------------------*/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20171107

(1 row(s) affected)

Select allOpen in new window

Good luck

This works fine in a basic SQL script but when loading the value to my table I'm getting the
'20171e+007' value.

what value you trying to save in your target table? as a date or integer data type, etc?

if your original data type is a date, and you need to store it as a date as well, then you no need to format/ cast it before storing in the target table.

IF the target column is varchar(80) and the source column is date/datetime/smalldatetime/datetime2 then what you need is what you already have:

    convert(varchar(8),column_name,112)  -- which produces YYYYMMDD , and as the convert clearly states it is a varchar result

If the source column is NOT date/datetime/smalldatetime/datetime2 then you may be attempting to convert an out of bounds number into a date sting (so it has to be a valid date first)

Here is a table of convert baseddate styles (formats) . However as others have already stated you an use FORMAT() instead with more recent versions.

BUT!
Do NOT store dates as strings, this is almost never a good idea. It is slow and it consumes a lot of post processing.

Please clarify what the source data is (string? number? what data type is it)
some examples would be good

the results in my target table look like this...  '20171e+007'.

This is scientific notation so seems to be that you're using the wrong datatype for that column (should be date or datetime and you're using a numeric datatype).

also wondering if data such as "20171e+007" were imported from another source, which its original values could already been tampered.

The target column is varchar(80).

As Paul mentioned above, that is not a good idea... It would be way better if you made the target column an int.

Sorry for the delay.  Some answers to the above.  
--Version 2008 R2
--Source column = datetime / target column = varchar 80  (source and target columns not my design)
--This works in a straight SELECT statement -  convert(varchar(8),column_name,112) - but when I add it to a SP to load the data into the target     table, I get the scientific notation.  Target column = varchar(80) format.
--Source appears as 2016-11-01 00:00:00:000

Thanks!

Have u tried my suggestion.

Yes.  Same result.  It returns the right value in a SELECT statement but when the data is loaded to the table I see the scientific notation.  The 'Value' column is varchar (80).  Obviously, it has something to do with populating the table but I don't have a clue what it is.  There are other data values populating the value column coming from other UNIONS in the SP and they appear correct.  So I'll see '201710' which is correct for this part of the SP.  The section with the issue should be returning something like '20171031'.   Additional thoughts are welcome and appreciated!

Can you send us the sp code? Would like check that

I can't send the SP due to confidentiality but this is part of an INSERT to a table.  Select blah, blah, blah...  inserting into a varchar(80) field called 'Value'.  I guess the question is what conditions would need to be in place to force the value to be converted to scientific notation?  Generally it happens when the column size can't accommodate the size of the value populating the field.  

,'Input' as [CURRENCY]
,'Contr_Ed_Mth' as [MEASURE]
,SUBSTRING(CAST(REV.CTRCT_END_DT as Varchar(6)),5,2) as [VALUE]

ValentinoV - so if I move this section of the code to the beginning instead of the middle / end, it might have an impact?

At least provide us insert and select query ...
I am sure you are inserting incorrect value,,,may be some concatenation is happening there..

Thanks!  This resolved my issue!