Author |
Topic |
cwtriguns2002
Constraint Violating Yak Guru
272 Posts |
Posted - 2007-03-14 : 05:42:30
|
Hi. I have date selected in a query and it returns 2007-02-08 00:00:00. How could i convert this to February 08, 2007 ?select datehired from hremployeesThanks.-Ron- |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-14 : 05:53:49
|
select datehired, convert(varchar, datehired, 107)from hremployeesPeter LarssonHelsingborg, Sweden |
|
|
cwtriguns2002
Constraint Violating Yak Guru
272 Posts |
Posted - 2007-03-14 : 06:18:00
|
the output is Feb 08, 2007.Is it possible to make it February 08, 2007 ?Thanks-Ron- |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-03-14 : 06:25:16
|
[code]select datename(month, getdate()) + ' ' + convert(varchar(10), datepart(day, getdate())) + ', ' + convert(varchar(10), datepart(year, getdate()))[/code] KH |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-14 : 06:40:52
|
Shorter, and with leading zero for day.select datename(month, current_timestamp) + ' ' + right('0' + datename(day, current_timestamp), 2) + ', ' + datename(year, current_timestamp)Peter LarssonHelsingborg, Sweden |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-03-14 : 21:59:11
|
Ok... I wanna know where all the "do it in the app" zealots are on this one... Boy! You guys are fickle...--Jeff Moden |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
cwtriguns2002
Constraint Violating Yak Guru
272 Posts |
Posted - 2007-03-14 : 22:08:06
|
Sorry for my ignorance but i really don't know how to insert the datehired in your queries.thanks.-Ron- |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-03-14 : 22:21:41
|
replace the getdate() with your column name datehiredselect datename(month, getdate() datehired) + ' ' + convert(varchar(10), datepart(day, getdate() datehired)) + ', ' + convert(varchar(10), datepart(year, getdate() datehired))from yourtable KH |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-03-15 : 08:29:34
|
quote: Originally posted by Jeff Moden Ok... I wanna know where all the "do it in the app" zealots are on this one... Boy! You guys are fickle...--Jeff Moden
I agree .... boy, where are those crazies who suggest doing it the easy and correct way by following best practices and formatting data at the presentation layer !? What a silly idea.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-03-15 : 08:39:20
|
Ron -- i strongly suggest that you read the thread that khtan gave you a link to.You should not format any data in T-SQL if you can avoid it, it is simply not designed for that. It is designed to calculate and return DATA, not format and present things. If you convert a DateTime to a VARCHAR, it is no longer a date, but just a string of characters that will not sort or compare correctly.Plus, it is much, much easier to do your formatting where it is supposed to be done, since clients are designed to do this very easily.How are you outputting your data? A report, a web page, etc ? THAT is where you can very simply format it any way you want, without any confusing CONVERT's, and will great flexibility, often just picking a simple "MMMM dd,yyyy"-type format string which is easy to edit and work with. As for the responses -- it would be really nice if people here actually suggested some best practices instead of making things more complicated for the people asking for help by giving convoluted hacks. Showing them ways to do things that are not considered best practices and that are complicated and have adverse affects probably is not helping them much in the long run to learn how to effectively use SQL Server. Every now and then you might need to write a CONVERT() like this, but 99% of the time it is not necessary and people should be taught to use SQL Server and their client tools correctly.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
cwtriguns2002
Constraint Violating Yak Guru
272 Posts |
Posted - 2007-03-16 : 04:26:29
|
I have read it... thanks.o well i have another problem in date, i think i can't do it through front-end application.table - hrsetholidaylegaldescription ---------month -------daychristmas -----------12-----------25independence ---------6-----------12holloween -----------11-----------1How could I convert it to the actual date?Expected result:description ---------month -------day----date----------monthchristmas -----------12-----------25-----12/25/2007 ---Decemberindependence ---------6-----------12-----6/12/2007-----Juneholloween -----------11-----------1------11/01/2007----NovemberThanks-Ron- |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-16 : 04:48:14
|
[code]declare @hrsetholidaylegal table ([description] varchar(20), [month] tinyint, [day] tinyint)insert @hrsetholidaylegalselect 'christmas', 12, 25 union allselect 'independence', 6, 12 union allselect 'holloween', 11, 1-- initdeclare @newyear intselect @newyear = 2007-- show the resultselect *, dateadd(month, 12 * @newyear - 22801 + [Month], [Day] - 1), datename(month, dateadd(month, 12 * @newyear - 22801 + [Month], [Day] - 1))from @hrsetholidaylegal[/code]Peter LarssonHelsingborg, Sweden |
|
|
cwtriguns2002
Constraint Violating Yak Guru
272 Posts |
Posted - 2007-03-16 : 05:02:41
|
got it.... thanks peso. That is a great thread.thanks-Ron- |
|
|
cwtriguns2002
Constraint Violating Yak Guru
272 Posts |
Posted - 2007-03-16 : 05:35:06
|
ops got error in report. The query runs perfectly in SQL Analyzer, but when i copy the query to my report i got the following error:An error occurred while reading while reading the data from the query result.Adding a value to a 'datetime' column cause overflow.weired... what does it mean?thanks.-Ron- |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-16 : 05:48:24
|
DATE can only accept dates between 1753-01-01 and 9999-12-31.Peter LarssonHelsingborg, Sweden |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-03-16 : 08:35:04
|
quote: I agree .... boy, where are those crazies who suggest doing it the easy and correct way by following best practices and formatting data at the presentation layer !? What a silly idea.- Jeffhttp://weblogs.sqlteam.com/JeffS
Heh... there they are --Jeff Moden |
|
|
cwtriguns2002
Constraint Violating Yak Guru
272 Posts |
Posted - 2007-03-17 : 01:17:54
|
Hi again.How could i pick the year from getdate() function? I mean, i want to retrieve the year only.declare @date intset @date = getdate()thanks-Ron- |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-03-17 : 01:25:27
|
[code]select datepart(year, getdate()), year(getdate())[/code] KH |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2007-03-17 : 13:46:12
|
If you need it as a string... SELECT DATENAME(yy,GETDATE())--Jeff Moden |
|
|
Next Page
|