Datetime issue

I am trying to run a query to get items that expire within the next 3 days. I got this off of another section of this website, but it will not work in Microsoft SQL Server 2000-Any ideas? Thanks.

SELECT *
FROM INVENTORY_TBL
WHERE PERISHABLE_DATE BETWEEN DATE_ADD
(CURRENT_DATE, INTERVAL 3 DAY) AND CURRENT_DATEYou need to look up the syntax of DATEADD in Books Online.

SELECT *
FROM INVENTORY_TBL
WHERE PERISHABLE_DATE BETWEEN DATE_ADD
(day, 3, CURRENT_DATE) AND CURRENT_DATE|||Okay-didn't think to do that-I had just done a datetime search-Would I be correct that the following would return anything within 7 days?

SELECT *
FROM INVENTORY_TBL
WHERE PERISHABLE_DATE BETWEEN DATEADD (DD, 7, GETDATE()) AND GETDATE()

It isn't returning any data, so if I am doing correctly, I have something wrong in my insert statement.

Thanks.|||wherever you got that from, it doesn't work

not even if you convert the syntax to sql server syntax :)

the reason is, the dates need to be reversed

this will not work --

... where perishable_date between dateadd(day,3,current_date) and current_date

this might --

... where perishable_date between current_date and dateadd(day,3,current_date)

the reason i say "might" is because you need to find what microsoft sql server uses instead of the standard sql current_date function

;)|||whoops, you already found GETDATE() -- congratulations!

now just change your query around and you'll be in business|||I switched the dates and it works. Thank you.

SELECT *
FROM INVENTORY_TBL
WHERE PERISHABLE_DATE BETWEEN GETDATE() AND DATEADD(DD, 7, GETDATE())

DateTime Format -

I have installed the trial version of windows server 2003 on the second hard drive on my computer. I set up IIS and ran my website on it but the problem is when I do something on the site, which has a sqlinsert statement regarding datetime.now it says, "conversion failed when converting datetime from character string"

I think it's to do with the clock on server 2003, the format is like: 11/07/2007 2:39:59 a.m.

I think it should be in formatAM and not a.m.

Any ideas on how to change the time format on a computer?

Or should I just change the Columns in my table to a Nvarcher value or something?

thanks

how is the value coming through? from your application? via now() ?

|||

Hi,

Thanks for your reply

What do you mean via now()?

I'm using VB and if I use something like. sqldatasource1.insertparameters.add("enddate", datetime.now()) it will give the format: 11/07/2007 2:39:59a.m.(which gives the incorrect string error.) when it should be 11/07/2007 2:39:59AM,

It must be to do with the computer clocks date time format, on server 2003 ?

Any ideas?

|||

Hi,

Please run the "Regional and Language Options" in your Control Panel. Click on "Customize", and switch to the Time tab, just to modify the "AM symbol" and "PM symbol" and hava a try.

Good Luck.

|||

store the datetime column in international format or use now.tostring("format eg. MM/dd/yyyy hh:mm:ss etc ")

|||

If the SqlDbType = DateTime then format should not come into it as the output string display is just a human readable format for display use that is not used by SQL when feeding DateTime values into it.

Do you have a snippet of the code? Something like this is what I would expect for a successful date insertion: (example routine)

public static bool InsertDateIntoRandomTable() {bool blSuccess =false;string strComm ="INSERT INTO [RandomTable] " +"(One_Date) VALUES (@.One_Date)"; SqlConnection sqlConn =new SqlConnection(strGlobalSQLConnection); SqlCommand sqlComm =new SqlCommand(strComm, sqlConn); sqlComm.Parameters.Add("@.One_Date", SqlDbType.DateTime).Value = DateTime.Now; sqlConn.Open();if (sqlComm.ExecuteNonQuery() > 0) blSuccess =true; sqlConn.Close();return blSuccess; }

Hope this helps

Mark

|||

Hi,

Thanks for the help guys

I tried what you said and it changed the clock on the computer OK. But strangley, on the website; it is still doing the format 11/11/2006 12:07a.m. instead of 11/11/2006 12:07AM

Is it something to do with IIS settings?

Thanks

|||

Hi,

After you change the time format in Regional and Language Options, you shouldrestartthe Visual Studio and open your application project, build and run the application again. Then check it and explorer the page in your IIS.

Thanks.

|||

Thanks a lot for your help. I tried restarting my computer etc. but, no luck...

Datetime - Regional Settings

Hi,

I am using an ODBC connection with Visual Interdev 6.0 to connect to a SQL datbase and display SQL tables on a website. I am having difficulty displaying the datetime in the correct format.

It appears that the datetime is being displayed in a format that is independent of the REGIONAL settings on either the client or the server! If I change the regional settings on either machine, the datetime is still displayed in the same format.

Any ideas as to how to solve this much appreciated!::I am using an ODBC connection with Visual Interdev 6.0 to connect to a SQL datbase and
::display SQL tables on a website

These problems could be rlated to our understanding of the stuff you are using. Basically, you seem, in addition, not to be aware of the difference between ASP and ASP.NET.

ASP.NET is the successor of ASP - and has nothing in common, codewise and usage wise.

THESE forums HERE are talking about ASP.NET.

I suggest you take your ASP question to news.microsoft.com - into the ASP groups there.

Getting a sensible answer ehere would be luck - I personally have stoopped working with ASP and Interdev yeawars ago ahd hardly remember anything from these hard times.|||Pass the date pre-formatted as a string to the front-end.

dates

I have a view that shows me how many visits i have had on my website.
UniqueVisits(number), TheYear(2005 (using datepart)), TheMonth(April (using
datename)), TheDay (Sunday (using datename),TheDate (10 (using datepart)).
The result is like this:
.......
23, 2005, April, Sunday, 10
So for April i so far has 10 records since it is April 10.
The table is like this:
CREATE TABLE [dbo].[T_PageStat] (
[IDStat] [int] IDENTITY (1, 1) NOT NULL ,
[DateRegistered] [datetime] NULL ,
[Counter] [numeric](18, 0) NULL ,
[IPAddress] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
[BrowserData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL ,
[LanguageData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL
) ON [PRIMARY]
As you see i am collecting the date the visitor entered, a counter telling
me how many times this visitor entered, the IP, what kind of browser, and
finally the language the user has set in browser language. I use SPROC to
populate the table
I want to update my view so that i can get a record for every day in the
month even tho it is only April 10. The rest of the days will be 0
(11,12....)
Not sure how to do that so i was hoping for some help.
Any tip will be appreciated. I am using a SQL 2000 server
Best regards, Trond
The code for the view:
CREATE VIEW dbo.statUniquePrMonth
AS
SELECT TOP 100 PERCENT COUNT(IDStat) AS UniqueVisits, DATEPART(YYYY,
DateRegistered) AS TheYear, DATENAME(month, DateRegistered) AS TheMonth,
DATENAME(dw, DateRegistered) AS TheDay, DATEPART(dd,
DateRegistered) AS TheDate
FROM dbo.T_PageStat
GROUP BY DATEPART(YYYY, DateRegistered), DATENAME(month, DateRegistered),
DATENAME(dw, DateRegistered), DATEPART(dd, DateRegistered)
HAVING (DATEPART(YYYY, DateRegistered) = DATEPART(YYYY, GETDATE())) AND
(DATENAME(month, DateRegistered) = DATENAME(month, GETDATE()))
ORDER BY DATEPART(dd, DateRegistered)Hi
This may be easiest with a calander table e.g
http://www.aspfaq.com/show.asp?id=2519
You can then use an outer join to get all the days in the given month.
John
"Trond" <thoiberg@.broadpark.no> wrote in message
news:4258d99c$1@.news.broadpark.no...
>I have a view that shows me how many visits i have had on my website.
> UniqueVisits(number), TheYear(2005 (using datepart)), TheMonth(April
> (using datename)), TheDay (Sunday (using datename),TheDate (10 (using
> datepart)).
> The result is like this:
> .......
> 23, 2005, April, Sunday, 10
> So for April i so far has 10 records since it is April 10.
> The table is like this:
> CREATE TABLE [dbo].[T_PageStat] (
> [IDStat] [int] IDENTITY (1, 1) NOT NULL ,
> [DateRegistered] [datetime] NULL ,
> [Counter] [numeric](18, 0) NULL ,
> [IPAddress] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
> [BrowserData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL ,
> [LanguageData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL
> ) ON [PRIMARY]
> As you see i am collecting the date the visitor entered, a counter telling
> me how many times this visitor entered, the IP, what kind of browser, and
> finally the language the user has set in browser language. I use SPROC to
> populate the table
>
> I want to update my view so that i can get a record for every day in the
> month even tho it is only April 10. The rest of the days will be 0
> (11,12....)
> Not sure how to do that so i was hoping for some help.
> Any tip will be appreciated. I am using a SQL 2000 server
> Best regards, Trond
> The code for the view:
> CREATE VIEW dbo.statUniquePrMonth
> AS
> SELECT TOP 100 PERCENT COUNT(IDStat) AS UniqueVisits, DATEPART(YYYY,
> DateRegistered) AS TheYear, DATENAME(month, DateRegistered) AS TheMonth,
> DATENAME(dw, DateRegistered) AS TheDay, DATEPART(dd,
> DateRegistered) AS TheDate
> FROM dbo.T_PageStat
> GROUP BY DATEPART(YYYY, DateRegistered), DATENAME(month, DateRegistered),
> DATENAME(dw, DateRegistered), DATEPART(dd, DateRegistered)
> HAVING (DATEPART(YYYY, DateRegistered) = DATEPART(YYYY, GETDATE()))
> AND (DATENAME(month, DateRegistered) = DATENAME(month, GETDATE()))
> ORDER BY DATEPART(dd, DateRegistered)
>