Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 date

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 hremployees

Thanks.
-Ron-

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 05:53:49
select datehired, convert(varchar, datehired, 107)
from hremployees



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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-
Go to Top of Page

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

Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 22:05:13
Sorry Jeff. I ran out of copyright license. Need to get more license from Madhi.

Ron, take a look at this thread. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76862.
You will get some lots of idea from it.




KH

Go to Top of Page

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-
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 22:21:41
replace the getdate() with your column name datehired

select datename(month, getdate() datehired) + ' ' +
convert(varchar(10), datepart(day, getdate() datehired)) + ', ' +
convert(varchar(10), datepart(year, getdate() datehired))
from yourtable



KH

Go to Top of Page

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.



- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

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.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

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 - hrsetholidaylegal
description ---------month -------day
christmas -----------12-----------25
independence ---------6-----------12
holloween -----------11-----------1

How could I convert it to the actual date?

Expected result:
description ---------month -------day----date----------month
christmas -----------12-----------25-----12/25/2007 ---December
independence ---------6-----------12-----6/12/2007-----June
holloween -----------11-----------1------11/01/2007----November

Thanks
-Ron-

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 04:38:14
See this topic how to get a SQL date from Year, Month and Data values.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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 @hrsetholidaylegal
select 'christmas', 12, 25 union all
select 'independence', 6, 12 union all
select 'holloween', 11, 1

-- init
declare @newyear int

select @newyear = 2007

-- show the result
select *,
dateadd(month, 12 * @newyear - 22801 + [Month], [Day] - 1),
datename(month, dateadd(month, 12 * @newyear - 22801 + [Month], [Day] - 1))
from @hrsetholidaylegal[/code]

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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-
Go to Top of Page

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-
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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.

- Jeff
http://weblogs.sqlteam.com/JeffS




Heh... there they are

--Jeff Moden
Go to Top of Page

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 int
set @date = getdate()

thanks
-Ron-
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-17 : 01:25:27
[code]
select datepart(year, getdate()), year(getdate())
[/code]


KH

Go to Top of Page

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
Go to Top of Page
    Next Page

- Advertisement -