Dates and SQL statement - 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

Dates and SQL statement

Author

Topic

Spangle1187
Starting Member

14 Posts

Posted - 2010-11-22 : 05:59:27

I am trying to construct an SQL statement that will return information on the current date. I am working on a project in visual studio 2010 and would like for this sql to work upon page_load

SELECT * From Booking

WHERE BookingDate = todays date

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-22 : 06:13:43

I assume this is SQL Server 2008?

WHERE BookingDate = CONVERT(DATE,GETDATE())

This will omit the time part.
But I am assuming also that your BookingDate is of datatype DATE.

If that isn't what you are looking for then be more specific please i.e. provide us SQL Server version, DDL, sample data and sample output.

No, you're never too old to Yak'n'Roll if you're too young to die.

Spangle1187
Starting Member

14 Posts

Posted - 2010-11-22 : 06:21:19

sorry should have inclueded more information.

Firstly thank you for your reply

Yes it is SQL Sever 2008

I will try what you have sugested, I did try DATEDIFF but it did not like this. I will let you know how I get on, again thanks

Spangle1187
Starting Member

14 Posts

Posted - 2010-11-22 : 06:31:29

SELECT * FROM [Booking] WHERE BookingDate = CONVERT(DATE,GETDATE())

This is the code I am now trying and returns the following error when I test it

"There was an error executing the query. Please check the syntax of the command and if present, the typee and values of the parameters and enure they are correct. Undefined function 'CONVERT' in expression"

I tried to add this code to an sql data source taken from the toolbox in VS. The database is from Access and has been added via a data connection. The data is to be displayed on the page though a grid view

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 06:35:20

What datatype is BookingDate?
Are you really running this on sql server and v2008?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Spangle1187
Starting Member

14 Posts

Posted - 2010-11-22 : 06:43:47

BookingDate is date/time (the only option in access 2007)

I created the tables in access while I was waiting for VS to be delivered and then connected them together. VS2010 is the version I have so I am guessing that this would run sql server 2008 rather than 2005 that was build into the previous version?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 06:46:50

I suspect the query is being executed in access rather than sql server which is why it is saying that convert is an invalid function.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Spangle1187
Starting Member

14 Posts

Posted - 2010-11-22 : 06:52:10

Now that I have visual studio would it be best to recreate the tables in VS and delete the data connection and work with them all under one roof so to speak?

Also, do these kinds of compaterbility issues happen oftern as I (and I am a novice) thought that sql was a universal language?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 07:02:35

>> thought that sql was a universal language
Nope.
There is an ansi sql standard (several versions of it).
There used to be a compliance certification but that was dropped.
All vendors have their own implementations - usually with a basis in the ansi standard but with extensions for performance or functionality or just because that was how they decided to implement it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Spangle1187
Starting Member

14 Posts

Posted - 2010-11-22 : 07:32:44

Well you learn something new everyday

Spangle1187
Starting Member

14 Posts

Posted - 2010-11-22 : 07:35:20

would you recommend recreating the tables in VS

Spangle1187
Starting Member

14 Posts

Posted - 2010-11-22 : 08:56:03

I have deleted the data connection and created the tables in VS so everything is under one roof. I have tried the sql query:

	Posted - 11/22/2010 : 06:31:29 SELECT * FROM [Booking] WHERE BookingDate = CONVERT(DATE,GETDATE())

But I am still getting the following error

"There was an error executing the query. Please check the syntax of the command and if present, the typee and values of the parameters and enure they are correct. Undefined function 'CONVERT' in expression"

The select half of the query is fine

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 09:00:35

Not sure how you are running this.
load sql server management studio and run the statement in a query window.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Spangle1187
Starting Member

14 Posts

Posted - 2010-11-22 : 09:08:12

I can find the configeration manager for sql server 2008 but not sever management studio is this something that I should download?

Spangle1187
Starting Member

14 Posts

Posted - 2010-11-22 : 10:43:31

[code] <asp:SqlDataSource ID="SqlDataSource1" runat=server
ConnectionString="<%$ ConnectionStrings:bookingOverview %>"
ProviderName="<%$ ConnectionStrings:bookingOverview.ProviderName %>"
SelectCommand="SELECT BookingID, RoomID, ClientName, BookingDate, StartTime, EndTime, Subject FROM Booking WHERE (BookingDate = lblSelectedDate.[Text])">
<SelectParameters>
<asp:ControlParameter ControlID="lblSelectedDate" Name="selectedDate"
PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>[/code]

I have tried to link the sql statement to lbloutput, this is not working either. The label data is populated from the followign code:

[code] lblSelectedDate.Text = DateTime.Now.ToString("dd,MMMM,yyyy")[/code]

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 11:00:16

What you have there will pass the text 'lblSelectedDate.[Text])' to the server not the contents.
You need to get the value and pass that.
For sql server you would need to format it as 'yyyymmdd'
so you need to generate the command string as
SelectCommand="SELECT BookingID, RoomID, ClientName, BookingDate, StartTime, EndTime, Subject FROM Booking WHERE (BookingDate = 'yyyymmdd'">

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources