DATEPART vs DATENAME - SQL Server Forums

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

DATEPART vs DATENAME

Author

Topic

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 08:08:14

I have the following piece of code:

UPDATE TicketMetrics SET TicketOpenDate = DATENAME(YEAR(TicketOpenDateTime),DATENAME(MONTH(TicketOpenDateTime),DATENAME(DAY(TicketOpenDateTime))))

What I am looking for is simply the year, month, and day displayed in a column without the time that is currently part of TicketOpenDateTime. So for example, the TicketOpenDateTime currently shows 2009/8/15 10:15:00 AM and I only want the 2009/8/15.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 08:10:38

UPDATE TicketMetrics SET TicketOpenDate =dateadd(day,datediff(day,0,TicketOpenDate ),0)

Madhivanan

Failing to plan is Planning to fail

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 08:28:53

sorry for the noob question, but why are you using DateDiff, that gives the difference between two dates right? All I want is the date part of the value in TicketOpenDateTime. I would have thought it would be DatePart. I admit I am still confused.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 08:31:24

quote:
Originally posted by JVisconti

sorry for the noob question, but why are you using DateDiff, that gives the difference between two dates right? All I want is the date part of the value in TicketOpenDateTime. I would have thought it would be DatePart. I admit I am still confused.

See this
select getdate(),dateadd(day,datediff(day,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 08:33:47

I tried this piece in an attempt to understand:

UPDATE TicketMetrics SET TicketOpenDate = DATEPART(year,TicketOpenDateTime)

This gives me the year from TicketOpenDateTime, now I want to also return the month and day. Can I do that all in the same UPDATE statement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 08:36:45

do you mean this?

UPDATE TicketMetrics SET TicketOpenDate = DATEADD(dd,DATEDIFF(dd,0,TicketOpenDateTime),0)

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 08:39:55

the current data type is datetime not int. So I was trying to only pull the date out of TicketOpenDateTime. I know how I would do it in Excel, but the change into T_SQL is messing me up.

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 08:41:47

OK the last piece of code you gave me does work, now I want to remove the time part of that return from showing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 08:41:53

quote:
Originally posted by JVisconti

the current data type is datetime not int. So I was trying to only pull the date out of TicketOpenDateTime. I know how I would do it in Excel, but the change into T_SQL is messing me up.

did it work fine?

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 08:43:39

Yes, now I want to remove the time stamp from the return of the code you gave me.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 08:51:16

quote:
Originally posted by JVisconti

Yes, now I want to remove the time stamp from the return of the code you gave me.

Have you ever tried the solution I posted?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 08:58:31

quote:
Originally posted by JVisconti

Yes, now I want to remove the time stamp from the return of the code you gave me.

my posted query does not have time part. it will have 00:00:00. if your question is to remove that, then do it using front end date functons

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 09:04:22

Madhivanan,
I put in the code piece you gave. It returned todays date with the time in one column, and in a second column returned the same date with the time in all zero's. I want the time to not even show up.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-29 : 09:10:24

quote:
Originally posted by JVisconti

Madhivanan,
I put in the code piece you gave. It returned todays date with the time in one column, and in a second column returned the same date with the time in all zero's. I want the time to not even show up.

If the column is datetime datatype, you can't do this
Format the date in the front end application

Madhivanan

Failing to plan is Planning to fail

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 09:17:25

Possible conversions would be varchar, int, or float correct? I am not as sure of myseld in tSQL as I am in C#. :)

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 09:38:59

Also, If I want to pull just the time from that same column, could I not just change the dd in DATEADD to hour, or hh? Now this will return only the hour from the TicketOpenDateTime column in the 24hr increment way, i.e, 13:00:00, and I need to return the entire time set.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 13:26:17

for getting entire timepart use

DATEDIFF(dd,DATEDIFF(dd,0,datefield),datefield) then you get datetime value as 1900-01-01 hh:mm:ss . to get time alone use CONVERT(varchar(8),DATEDIFF(dd,DATEDIFF(dd,0,datefield),datefield),108)

JVisconti
Starting Member

47 Posts

Posted - 2009-09-29 : 13:30:16

Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 13:32:53

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources