Multiple fields as an alias - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Multiple fields as an alias

Author

Topic

rreno
Starting Member

4 Posts

Posted - 2004-10-15 : 14:25:14

I am trying to make 3 fields into one date field in a SQL server. The fields are: S_Day, S_Month and S_Year I would like to combine them all into 1 date... mm/dd/yyyy

This is an ASP application connecting to the server through ODBC.

The original application was written in Access and they used cDate() to combine the fields... SQL Server will not accept this.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]'CDate' is not a recognized function name.

/invoicingsystem/control/admin_reservation.asp, line 94

Here is the original code.

(Line 94): strSQL = strSQL & "AND CDate(S_Month & '/' & S_Day & '/' & S_Year) = #" & CDate(dDate) & "# "

I am trying to write a view where I can just ask for the date normally.

Thanks,
Rob

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-15 : 16:16:31

strSQL = strSQL & " AND convert(Datetime, S_Month + '/' + S_Day + '/' + S_Year) = '" & CDate(dDate) & "' "

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

rreno
Starting Member

4 Posts

Posted - 2004-10-18 : 11:47:07

Thanks for the response tuenty. However, I am getting a different error now.

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value '/' to a column of data type int.
/invoicingsystem/control/admin_reservation.asp, line 95

I also tried to change the + to a & but it threw the same error.
strSQL = strSQL & " AND convert(Datetime, S_Month + '/' & S_Day + '/' + S_Year) = '" & CDate(dDate) & "' "

strSQL = strSQL & " AND convert(Datetime, S_Month & '/' & S_Day & '/' & S_Year) = '" & CDate(dDate) & "' "

Thanks again,
Rob

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-18 : 11:57:02

are S_Month, S_Day, and S_Year int columns??
What are the values the values of those columns and what are you sending in dDate??

Maybe you have to convert each column from int to char using cast or convert
or you can eliminate the cdate

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

rreno
Starting Member

4 Posts

Posted - 2004-10-18 : 16:55:27

Yes, the fields are int columns.

SELECT * FROM qrySchedule_All WHERE 1=1 AND convert(Datetime, S_Month + '/' & S_Day + '/' + S_Year) = '10/29/2004'

One other thing I did was take out the '/' and left the +:
SELECT * FROM qrySchedule_All WHERE 1=1 AND convert(Datetime, S_Month + S_Day + S_Year) = '10/29/2004'

This worked, but it returned no records... even though it should have returned some.

I have no idea what "cast" is.

Thanks again for your responses.

Rob

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-18 : 17:23:17

use cast to convert each int column to a char column before concatening the fields that build the date

declare @month int, @day int, @year int, @aa as bit
select @month=10, @day=18, @year=2004
if convert(Datetime, cast(@month as char(2)) + '/' + cast(@day as char(2))+ '/' 
       + cast(@year as char(4))) = '10/18/2004' 
    set    @aa = 1 
else 
    set    @aa = 0

print @aa

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

rreno
Starting Member

4 Posts

Posted - 2004-10-18 : 17:32:05

THANKS!!! With a little tweaking I was able to make your code work:

strSQL = strSQL & " AND convert(Datetime, cast(s_month as char(2)) + '/' + cast(s_day as char(2))+ '/' + cast(s_year as char(4))) = '" & CDate(dDate) & "' "

Thanks for sticking with me.. please find the attached check for your services. (please note that some ISP's strip checks for their own use, in which case, please accept my sincere thanks)

Rob

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-19 : 10:54:04

quote:
Originally posted by rreno

THANKS!!! With a little tweaking I was able to make your code work:

strSQL = strSQL & " AND convert(Datetime, cast(s_month as char(2)) + '/' + cast(s_day as char(2))+ '/' + cast(s_year as char(4))) = '" & CDate(dDate) & "' "

Thanks for sticking with me.. please find the attached check for your services. (please note that some ISP's strip checks for their own use, in which case, please accept my sincere thanks)

Rob

I'm glad to be helpfull, You Welcome