How do I cast date format in MS SQL Server like 2010-xx-xx and as char(4) char(2) char(2) using also concatenating?

Posted on 2010-08-27
Last Modified: 2012-05-10
How do I cast date format in MS SQL Server like 2010-xx-xx and as char(4) char(2) char(2) using also concatenating?

Now, what I get is 2010-5 -16 and 2010-5 -4

Also, after executing command I get order by like this: 2010-5 -16 before 2010-5 -4 (because the order by order digit by digit). So I need to have also zero displayed, like 2010-05-16, 2010-05-04 and not like date format but like varchar format using both cast and concatenating.
Question by:hermesalpha
  • 4
  • 3
LVL 58

Expert Comment

ID: 33547797
select convert(char(10), dtcol, 120)

Author Comment

ID: 33547831
I received this error message when trying to execute the query:

Msg 207, Level 16, State 1, Line 8
Invalid column name 'DTCOL'.
Msg 104, Level 16, State 1, Line 18
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Author Comment

ID: 33547836
I have a Union in the first Select part with three columns that are concatenated into one
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 58

Expert Comment

ID: 33547837
dtcol represents your column that has the datetime.  If it is received_date then you would use

select invoice_id, invoice_number, customer_name, convert(char(10), received_date, 120)
from invoice

or if you want to work on the current datetime, then

select * from payment
where paid=0 and due_date <= convert(char(10), getdate(), 120)
LVL 58

Expert Comment

ID: 33547846
So the columns are int for year,month,day?
The following works whether y/m/d are int or char, as long as the they are numeric.

select convert(char(10),convert(datetime,convert(char(8),y*10000 + m*100 + d)),120)
from tbl

Author Comment

ID: 33547853
Yes, it worked now, thank you! One thing I don't understand: the number 120?

Is it something that represents the format of the date?
LVL 58

Accepted Solution

cyberkiwi earned 500 total points
ID: 33547864
Yes, it is a date format.

There isn't a format that is YYYY-MM-DD exactly, but by using 120 (yyyy-mm-dd hh:mi:ss(24h)) and forcing it into Char(10), the result is yyyy-mm-dd only.


Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now