varchar date problem - 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

varchar date problem

Author

Topic

Todd2006
Starting Member

18 Posts

Posted - 2007-01-28 : 11:26:18

have a field called sigdate datatype varchar that saves dates

like these

01/2/2007
12/2/2007
06/19/2007
01/21/2007

i want to show distinct months/year in a drop down

so the query should output

01/2007
12/2/2007
06/2007

here is my code

SELECT Distinct(convert(varchar(2),month([sigdate]))+convert(varchar(4),year([sigdate]))) as [monthyear] from stdreg

but its not working

any ideas

todd

Kristen
Test

22859 Posts

Posted - 2007-01-28 : 11:47:07

[code]
SELECT DISTINCT DATEADD(Month, DATEDIFF(Month, 0, [sigdate]), 0)
FROM stdreg
[/code]
or
[code]
SELECT DATEADD(Month, DATEDIFF(Month, 0, [sigdate]), 0),
COUNT(*)
FROM stdreg
GROUP BY DATEADD(Month, DATEDIFF(Month, 0, [sigdate]), 0)
[/code]
will give you the date of the first day of the month. You could then format that to get rid of the "day" bit if necessary.

Kristen

Todd2006
Starting Member

18 Posts

Posted - 2007-01-28 : 13:32:58

Thanks for ur reply

I want the month/year also

so the query you posted will give the month

i want it like this

01/2007

month/year

any idea

todd

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-28 : 15:00:38

SELECT datename(month, [sigdate]) + '/' + datename(year, [sigdate])
COUNT(*)
FROM stdreg
GROUP BY datename(month, [sigdate]) + '/' + datename(year, [sigdate])

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-01-28 : 15:25:52

"I want the month/year also

so the query you posted will give the month"

You didn't try it then?

It gives the 1st of each month, as I said; so that includes the Month and the Year that you require. You can format the result using CONVERT, or better still using your Client-side application.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources