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?
 
PortletPaulConnect With a Mentor freelancerCommented:
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
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Pawan KumarDatabase ExpertCommented:
edited my last comment.
0
 
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 ChongCommented:
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
 
PortletPaulfreelancerCommented:
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 ChongCommented:
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
 
ValentinoVConnect With a Mentor BI 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
 
ValentinoVConnect With a Mentor BI 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.