SQL Server datetime formatting

Good evening, Gurus,
I need a datetime column to be presented in the 'YYYYMMDD' format.  I've tried all kinds of CONVERT and CAST statements and the results in my target table look like this...  '20171e+007'.  This should be 20171031.  The latest and greatest code =
convert(varchar(10),column_name,112)).  This works fine in a basic SQL script but when loading the value to my table I'm getting the
'20171e+007' value.  The target column is varchar(80).  I've tried DATEPART and just about everything else I can google. Recommendations are very much appreciated!
Cheryl McCormickConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Which SQL Server of version are you using?
0
Pawan KumarDatabase ExpertCommented:
This will work on SQL Server 2012 and above

SELECT FORMAT(GETDATE() ,'yyyyMMdd')

OUTPUT

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

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

(1 row(s) affected)

Open in new window

0
Pawan KumarDatabase ExpertCommented:
edited my last comment.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Pawan KumarDatabase ExpertCommented:
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)

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

Open in new window


Good luck
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
1
PortletPaulEE Topic AdvisorCommented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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).
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
also wondering if data such as "20171e+007" were imported from another source, which its original values could already been tampered.
0
ValentinoVBI ConsultantCommented:
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.
0
Cheryl McCormickConsultantAuthor Commented:
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!
0
Pawan KumarDatabase ExpertCommented:
Have u tried my suggestion.
0
Cheryl McCormickConsultantAuthor Commented:
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!
0
Pawan KumarDatabase ExpertCommented:
Can you send us the sp code? Would like check that
0
ValentinoVBI ConsultantCommented:
There are other data values populating the value column coming from other UNIONS in the SP and they appear correct

That's probably the cause of your issue.  When UNIONing datasets together the first SELECT in the whole query defines the data type of the columns. So your value is most likely getting converted to something numeric because of that...
1
Cheryl McCormickConsultantAuthor Commented:
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]
0
Cheryl McCormickConsultantAuthor Commented:
ValentinoV - so if I move this section of the code to the beginning instead of the middle / end, it might have an impact?
0
Pawan KumarDatabase ExpertCommented:
At least provide us insert and select query ...
I am sure you are inserting incorrect value,,,may be some concatenation is happening there..
0
PortletPaulEE Topic AdvisorCommented:
In the topmost part of the query.

For the column you are having trouble.

Use

      convert(varchar (80),  ....  )

To force that column to be varchar.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ValentinoVBI ConsultantCommented:
so if I move this section of the code to the beginning instead of the middle / end, it might have an impact?

It might have an impact yes but it's not the best approach. What Paul mentioned in his last post is a better way to fix this.
0
Cheryl McCormickConsultantAuthor Commented:
Thanks!  This resolved my issue!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.