extracting DATE from DATETIME variable

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

SQL Server 2005 Forums

Transact-SQL (2005)

extracting DATE from DATETIME variable

Author

Topic

agniwoni
Starting Member

28 Posts

Posted - 2007-09-27 : 03:40:39

Hi, the problem, as described in the subject:
how to extract DATE from a Datetime variable.

I used to use DATEPART and extract each year, month, day.. and after put it together in a varchar variable, separating with '-':

Datepart(YEAR, MyDate) + '-' + Datepart(MONTH, MyDate)+ '-' +
Datepart(DAY, MyDate) = 'YYYY-MM-DD'

Can I achieve this effect in other, mozre civilized way?
Thanks in advance for any help!
AgniWoni

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 03:44:18

You should do the date formatting at the front end, but other than that CONVERT can do the presentation you want

SELECT CONVERT(VARCHAR(10), GetDate(), 120)

Kristen

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 03:45:00

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Date%20formatting,Format%20Date

agniwoni
Starting Member

28 Posts

Posted - 2007-09-27 : 03:47:33

Thank You very much! :)

agniwoni
Starting Member

28 Posts

Posted - 2007-09-27 : 03:51:24

quote:
Originally posted by Kristen

You should do the date formatting at the front end, but other than that CONVERT can do the presentation you want

SELECT CONVERT(VARCHAR(10), GetDate(), 120)

Kristen

By the way... isn't CHAR(10) enough? This string lenght is never <> then 10 chars?
Regards!
A.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-27 : 03:53:02

If you want to compare date part omitting time part, then

Select........
where dateadd(day,datediff(day,0,datecol),0)=dateadd(day,datediff(day,0,@DATETIME_VARIABLE),0)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-27 : 03:54:36

quote:
Originally posted by agniwoni

quote:
Originally posted by Kristen

You should do the date formatting at the front end, but other than that CONVERT can do the presentation you want

SELECT CONVERT(VARCHAR(10), GetDate(), 120)

Kristen

By the way... isn't CHAR(10) enough? This string lenght is never <> then 10 chars?
Regards!
A.

Where do you want to show formatted dates?

Madhivanan

Failing to plan is Planning to fail

agniwoni
Starting Member

28 Posts

Posted - 2007-09-27 : 04:14:17

[/quote]
Where do you want to show formatted dates?

Madhivanan

Failing to plan is Planning to fail
[/quote]

First for grouping... but in future: who knows
A.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-27 : 04:33:59

quote:
Originally posted by agniwoni

Where do you want to show formatted dates?

Madhivanan

Failing to plan is Planning to fail

First for grouping... but in future: who knows
A.

If you want to group by just date, then use

Group by dateadd(day,datediff(day,0,datecol),0)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 05:16:56

If only GROUPING is considered, you only have to use

datediff(day, 0, datecol)

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 05:28:44

"isn't CHAR(10) enough? This string lenght is never <> then 10 chars?"

Sure, you can use CHAR(10). Not sure that's any different to VARCHAR(10) in this situation, but it might be more efficient if it avoids some additional Varchar handling to cope with variable length strings - which won't be needed of course.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources