3
Currently my seat is in YYYY-DD-MM format I would like to put in MM-DD-YYYY.
I found the SET DATEFORMAT only it’s not the best option since I’ll have to add it to all the querys.
Is it possible to configure it in my connection similar to what is done in Mysql?? Or change it somewhere so I don’t have to change it in Mysql querys?
3
If the idea is to use date literals in queries, just use the ISO format: YYYY-MM-DD (year, month and day). This will be interpreted smoothly in any environment.
To format (or "print") dates for text in format MM/DD/AAAA you can use the function CONVERT.
Example:
select convert(varchar, coluna_date, 101)
The same is valid to convert text in the format specified for a date:
select convert(datetime, coluna_varchar, 101)
My dear this is an alternative, but not one of the best since I will have to change all the script that has more than 500 queries. I thought I could set a certain configuration somewhere and thus remedy the problem.
@Iagoleão This is the correct way to implement to make the script independent of the server location. If your script has so many conversions hardcoded, you have 3 alternatives: (1) change everything (ever thought about using REGEX to automate this?); (2) if scripts are run via system, add a command SET DATEFORMT when getting the connection; (3) change the default language of the server (see this link, in English).
in the code there was 'cast((Convert(char(10),@perfim,101)+ '11:59:59 pm') as datetime)' only this generates this error Exception (0x80131904): The conversion of a data type varchar into a data type datetime resulted in a value outside the range. The instruction has been completed. ] so I removed the cast Convert and I passed the date in the YYYY-MM-AA format to work, only one place that keeps giving this same error even with the above-mentioned amendment. I wonder then whether it will not be less costly to apply 2° or 3° suggestion.
@Iagoleão I see that there is a lot of "manual" date manipulation. The "out of range value" error occurs when the date is in incorrect format, for example when trying to read a number greater than 12 as the date month. I imagine that this time being summed up would be to make a comparison of interval dates, right? There are other ways to do this. Have you ever thought of creating a FUNCTION to encapsulate these things and make maintenance easier?
this is a third-party code, in which our client requested that we make a migration since the other company stopped providing support, we will not improve and we will absolutely never do any implementation, since we seek the easiest solution and not the best, I don’t know if you understand me.
@I understand, and I understand the headache you must be giving. If the migration is of the type that is "done once and done", I suggest changing the language of SQL Server (option #3). Otherwise I suggest making the jutes according to option #1. There is also the option to make a regular expression to automatically add the SET DATEFORMAT at the beginning of all routines.
I’m sorry to bother you again. I was able to solve that date problem in several places, but there were two left in which I couldn’t even handle. Could you help me?
@Iagoleão If it is possible to isolate examples of this, post a new question that I take a look at.
Browser other questions tagged sql-server date
You are not signed in. Login or sign up in order to post.
I don’t understand your goal. You want to use literals to represent dates (e.g.:
2014-04-28) or wants to print the dates in some way?– utluiz