CODEWITHSUNDEEP
sqlsql-serverdateselect
Andreasp
Andreasp

Reputation: 45

sql - date not in format specified

I have searched a bit, and can't see any questions asked that answer my question, so here it goes.

I am trying to get a date from the database (source is in datetime format, i.e "2015-11-30 00:00:00.000") and select it as date, but in another format. I am using Convert function for this after "googleing". First I set target format as varchar:

Select
convert(varchar(10),ac_payout_book_dt,104) as 'Dato'

Result is date in correct format, i.e "30.11.2015"

Then I needed to sort it when automating the script, so to be able to use 

ORDER BY Dato DESC

... I changed my query to ...

Select    
convert(date,ac_payout_book_dt,104) as 'Dato'

Now the result is in the wrong format, i.e "2015-11-30"

After a few rounds on the internet I also tried this without any luck

CAST(convert(date,ac_payout_book_dt,104) AS DATE) as 'Dato'

Anyone that can help me to figure out where I am going the wrong way?

BR Andreas

Upvotes: 2

Views: 3914

Answers (3)

D Mayuri
D Mayuri

Reputation: 456

Convert 'Deto' in to 'date' format while sorting. Something like this - ORDER BY CONVERT(DATE, Deto, 104) DESC

Upvotes: 0

Chiragkumar Thakar
Chiragkumar Thakar

Reputation: 3716

i guess you should try this, order with CAST Date so by this way you will perform your desired operation and you will be able to get your desired date format as well.

SELECT 
    CONVERT(VARCHAR(10), ac_payout_book_dt, 104) as 'Dato'
ORDER BY
    CAST(ac_payout_book_dt AS DATE) DESC

Upvotes: 3

marc_s
marc_s

Reputation: 754488

Well, if you use

Select
    convert(varchar(10),ac_payout_book_dt,104) as 'Dato'

then you convert your DateTime (which internally in SQL Server has no format - it's an 8-byte binary value) to a string - you can control how the date is formatted into the string.

In order to sort your output, you need a date (without the time, I presume) - no problem, but when you do 

Select    
    convert(date, ac_payout_book_dt, 104) as 'Dato'

you're converting your DateTime to a Date (which in SQL Server again has no format - it's just a 3-byte bianry value) - so the style "104" is useless here - you're converting one binary value to another binary value - and the date will be displayed in the default SQL Server date formatting.

Since this really is a binary-to-binary conversion, without string formatting involved, I prefer to use

SELECT CAST(ac_payout_book_dt AS DATE) AS 'Dato'

since there's really no point in supplying a style which cannot be used anyway.

So really, what you need to be aware of is this:

  • are you using a DATE or DATETIME for sorting or date arithmetic? In that case, you must use the native data types - don't do date math on a string representation of a date!

  • do you want to output your DATE or DATETIME to the screen? Then you need to convert it to a string using CONVERT or FORMAT and supplying the proper styles / date formatting string

So overall, without knowing your entire query - you probably want to do something like this:

SELECT 
    CONVERT(VARCHAR(10), ac_payout_book_dt, 104) as 'Dato',
    ..... (other columns) .....
FROM
    dbo.YourTable
WHERE 
    .... (some condition) ....
ORDER BY
    CAST(ac_payout_book_dt AS DATE) DESC

Or maybe you want to check out FORMAT which is available from SQL Server 2012 on, that allows for more flexibility in formatting your dates:

SELECT
    FORMAT(ac_payout_book_at, 'dd.MM.yyyy', 'de-de')

Upvotes: 3

Related Questions