473,854 Members | 1,990 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_t ime,'mm/dd/yyyy')='10/04/03'

How do I accomplish the same in sqlserver?

Thanks in Advance
sk
Jul 20 '05 #1
4 61498
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>='20 031004' AND modif_time<'200 31005'

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
--
Jul 20 '05 #2
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!
Jul 20 '05 #3
sai (an*******@devd ex.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(datetim e, 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
Jul 20 '05 #4
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,12 0) AS modif_date,
COUNT(*)
FROM TEMP_TABLE
GROUP BY CONVERT(CHAR(10 ),modif_time,12 0)
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(CH AR(8),modif_tim e,112) AS DATETIME) AS modif_date,
COUNT(*)
FROM TEMP_TABLE
GROUP BY CAST(CONVERT(CH AR(8),modif_tim e,112) AS DATETIME)
ORDER BY modif_date

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
3912
by: Funnyweb | last post by:
I have just notices that the date() function is not returning the correct date/time on my "server". I am running apache2 on my winxp pro laptop. My system clock is set to the correct date, time and timezone, get the results returned by date() are 11 hours behind. Any ideas what is going wrong?
3
1677
by: Sharad Gupta | last post by:
Friends I need a date simple date function that would append the coming month value in my current month like monthname(month(date)) : gives me current month in string i need next month , a string value added to it. The problem I am facing is this.. prevmon = monthname(month(date) + 1)
2
2062
by: ltamisin | last post by:
Hi Im working on a Year(Date) function right now, the output of that function is this "2005", my question is how can i change the format into this "05" Function: Year(Date) Output: 2005 Needed Output: 05 Thanks
8
3035
by: Dennis M. Marks | last post by:
What is the maximum valid date range for the date(yyyy,mm,dd) function. -- Dennis M. Marks http://www.dcs-chico.com/~denmarks/ Replace domain.invalid with dcsi.net -----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
5
2440
by: Dennis M. Marks | last post by:
After reading section 15.9.1.1 the ECMAScript Language Specifications I see that the date range for the Date function is +/- 100,000,000 days from 01 Jan 1970. This is called an extrapolated Gregorian calendar. Since the Gregorian calendar did not begin until 15 Oct 1582 what is the purpose of dates before that date? Wouldn't any computation prior to that date be meaningless or am I missing something? The reason I ask is that I have...
6
39851
by: bryan.seaton | last post by:
I have a delete statement that is not doing what I want it to do: Delete from LOG_TABLE where (DATE(LOG_TS)) < (DATE(CURRENT_DATE)- 21 DAYS); It is supposed to delete all records that are 21 days or older than the current system date. Instead it is deleting all new rows. LOG_TS is a timestamp but that should not matter since DATE returns just the date portion of a date or timestamp...right? I'm not a SQL guru but I can't see...
4
1944
by: Christine | last post by:
I am having the strangest problem. I use the Date function in several of my forms and modules in an Access 2000 mdb. Lately, wherever in my code (in this one mdb) I use the Date function, it changes to date (lower case vs proper case). I can fix this very temporarily by reconstructing the database (importing all objects to a new mdb) or using the decomplie option to open it. But in either case, once I compile it reverts back to lower case....
4
1953
by: Patrick McGuire | last post by:
I need to get the current date in vb .NET. In vba I always used the Date() function, and the help in .NET indicates that this function still exists, but I can't seem to find it. The Now() function works just fine, but when I try to use Date(), I get an error. Am I missing a reference? What is it? Thanks, Pat
1
4675
by: shyam vashista | last post by:
i have problem in validation check for system date with server date problem::: If i change my system date as september 30, 2006 and use validation for filling form as current date as oct30, 2006 than it will ask me for check ur entry date but wen i fill form that time date is oct 30, 2006 so how to solve this problem ? how to check date with server date? it is not compulsary that system date by which i am filling form date is same...
3
4423
by: murch.alexander | last post by:
I made a simple public function to set and return a date value (see below). I have a number of queries that call up the function to get the "As Of Date," which is typically set to today's date. Occasionally though, I need to change the "As Of Date" to some date in the past, and then when I run the queries, they're based on that date in the past. This generally works fine, but, sometimes, the date gets reset to something WAY in the past...
0
9752
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10371
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7918
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7082
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5744
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5942
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4563
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4159
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3188
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.