How to use date function in sql server

[sk]

Hi

I am trying to do a simple select using a date value.

For eg:-
in oracle i would do the following
select count(*) from TEMP_TABLE where to_char(modif_time,'mm/dd/yyyy')='10/04/03'

How do I accomplish the same in sqlserver?

Thanks in Advance
sk

[David Portas]

You can use the CONVERT function to transform a DATETIME column to a string
in a particular date format. In a WHERE clause though it makes more sense
not to convert the dates - otherwise the conversion will force a table scan
and the conversion will have to be performed for every row.

Instead, specify the range of DATETIME values you require:

SELECT COUNT(*)
FROM temp_table
WHERE modif_time>='20031004' AND modif_time<'20031005'

You can use any of the following styles of formatted string to specify dates
in code:

'20031231'
'2003-12-31T17:59:00'
'2003-12-31T17:59:00.000'

These are the "safe" ISO formats which are guaranteed to work independently
of any regional settings. Other formats such as mm/dd/yyyy are best avoided
because they are dependent on the server's regional settings and are
therefore less portable.

--
David Portas
------------
Please reply only to the newsgroup
--

[sai]

David,

Tried your suggestion, works great. Thanks a lot.
I also tried using convert function to get counts for
multiple dates and works fine.
But I was trying to sort the rows using date, that
doesn't seem to work.

my query looks like this:-
select convert(varchar,modif_time,101),count(*) from TEMP_TABLE group by
convert(varchar,modif_time,101) order by convert(varchar,modif_time,101)

when I run it, the output looks like :-
01/01/2001
01/01/2002
01/01/2003
01/02/2001
01/02/2002
01/02/2003
01/03/2000
01/03/2001
01/03/2002
01/03/2003

As you can see, the sort order seems to be first 2 chars, then the next
2 chars and so on. I want it to be
01/03/2000
01/01/2001
01/02/2001
01/03/2001
01/01/2002
01/02/2002
01/03/2002
01/01/2003
01/02/2003
01/03/2003

this is the right date format. Is this possible? Any help
will be appreciated.

Thanks
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

sai (an*******@devdex.com) writes:

Tried your suggestion, works great. Thanks a lot.
I also tried using convert function to get counts for
multiple dates and works fine.
But I was trying to sort the rows using date, that
doesn't seem to work.

my query looks like this:-
select convert(varchar,modif_time,101),count(*) from TEMP_TABLE group by
convert(varchar,modif_time,101) order by convert(varchar,modif_time,101)

when I run it, the output looks like :-
01/01/2001
01/01/2002
01/01/2003
01/02/2001
01/02/2002
01/02/2003
01/03/2000
01/03/2001
01/03/2002
01/03/2003

As you can see, the sort order seems to be first 2 chars, then the next
2 chars and so on. I want it to be

Of course. You asked to sort on a character string, then SQL Server
will sort on a character string.

If you want to sort by date, there are two options:

1) Use a better date format, and still sort by string. Change 101 to
112 that is YYYYMMDD.

2) Use this somewhat convuluted query:

select convert(varchar, convert(datetime, dt), 101), cnt
from (select dt = convert(varchar, loadtime, 112), cnt = count(*)
from abasysobjects
group by convert(varchar, loadtime, 112)) as x
order by dt

(Column and table names changed to the table I used for the test.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Portas]

Because your CONVERT function returns a string you need to make sure the
string is in the correct format for sorting. (YYYY-MM-DD):

SELECT CONVERT(CHAR(10),modif_time,120) AS modif_date,
COUNT(*)
FROM TEMP_TABLE
GROUP BY CONVERT(CHAR(10),modif_time,120)
ORDER BY modif_date

You might prefer to output a date rather than a string - leave the
formatting of the date to your client application:

SELECT CAST(CONVERT(CHAR(8),modif_time,112) AS DATETIME) AS modif_date,
COUNT(*)
FROM TEMP_TABLE
GROUP BY CAST(CONVERT(CHAR(8),modif_time,112) AS DATETIME)
ORDER BY modif_date

--
David Portas
------------
Please reply only to the newsgroup
--