SQL CDate Conversion

0
October 14, 2016

Good Morning. I have a CDATE function in a MS Access query, which I am attempting to emulate in SQL Server. This is only my second time to create a query in SQL Server, so I'm a little green. I hope you can help. Here's my query and the design according to the query gridImage Text:

I'm also curious if you could advise how to handle the LTLeave field in SQL; here's how I have it in the MS Access query: IIf(Len([LeaveReason])>2,True,False)

And here's the date field I am having trouble with: MonthIndex1: IIf(CDate([tblMonthIndex].[ExchMonthEnd] & CStr(Year(Date())))

The entire SQL Statement:

SELECT Emptable.EmpNumber, UCase([Emptable].[FName]) AS FNAMED, UCase([Emptable].[LName]) AS LNAMED, IIf(Len([LeaveReason])>2,True,False) AS LTLeave, Min(MasterIssueReturnTable.IssueDate) AS IssueDate, Min(MasterIssueReturnTable.DueDate) AS DueDate, Emptable.RCnumber AS OldRCnumber, RCTABLE.RCnumber, Emptable.RCNumberParent, UCase([RCTABLE].[RCNAME]) AS RCNamed, RCTABLE.MonthExch1 AS RCMonthExch1, RCTABLE.MonthExch2 AS RCMonthExch2, UCase([tblDivision].[Division]) AS Divizion, Emptable.RecordingDiv, Emptable.Location, UCase([RCTable].[RCName]) AS RCNameParent, tblLocation.LocationName, IIf(CDate([tblMonthIndex].[ExchMonthEnd] & CStr(Year(Date())))1].[ExchMonthEnd] & CStr(Year(Date())+1)),CDate([tblMonthIndex1].[ExchMonthEnd] & CStr(Year(Date())))), RCTABLE.RCName;

0

answers

Comments See all(0)

Add comment

Related Questions

CDate conversion errors to SQL server views

I am converting several Access SQL statements to SQL server views and am receiving the following: "CDate is not recognized function name" when using one of the following SQL statements: SELE Read More

Views

65

Votes

0

Answers

5

August 17, 2011

SSRS BI error when hiding row conversion from string ""  to type Date is not valid

I have a report that does a Cdate on two fields to convert the date to a short format: =CDate(Fields!startdate.Va lue).ToSho rtDateStri ng() I then need to hide a row using: =Iif(Fields!enddate.Value Read More

Views

49

Votes

0

Answers

3

August 17, 2011

Date conversion in SSRS expression

Trying to get the last day of the previous month.  In SQL this works: select dateadd(dd,-1,dateadd(mm,d atediff(mm ,0,getdate ()),0)) When I try to use this in an SSRS expression, it doesn't Read More

Views

63

Votes

0

Answers

5

August 17, 2011

SQL-Only Date Conversion

I've done something lazy and now I'm paying the price. About a year ago I created a MySQL DB that I'm running on a local WebServer (MySQL 3.23.58). I'm using PHPMyAdmin and don't have direct access Read More

Views

41

Votes

0

Answers

0

June 20, 2016

Visual Studio .NET - Conversion from string "24/04/1967" to type 'Date

I have an error that is driving me nuts. Conversion from string "24/04/1967" to type 'Date' is not valid. The weird thing is that it works local but I only get the error on my production server..... Read More

Views

54

Votes

0

Answers

10

June 20, 2016

converting Access SQL statements to SQL 2000 Statements

I am having difficulty getting a simple counter I created in ASP using Access to work when converting for SQL 2000. I recently purchased SQL 2000 and imported the counter table into sql I immediately Read More

Views

75

Votes

0

Answers

16

August 16, 2011

Stored Procedures error

Microsoft OLE DB Provider for SQL Server error '80040e14' Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query. /ezservices/onlinebonds Read More

Views

82

Votes

0

Answers

4

August 16, 2011

MS SQL &ASP: LCID, datetime

Hello, i have been having this problem and been posting comments on this site, but no solution has been found yet. I want to ask, the imporatance of the datetime value on SQL Server. REason being. Whe Read More

Views

56

Votes

0

Answers

2

August 17, 2011

What is wrong with this query and what is the correct syntax

SELECT OrderID, cdate([OrderDate]), ShipCity FROM [Copy Of dbo_Orders] WHERE (([OrderDate] >= ?) AND ([OrderDate] <=?)) If I enter 10/1/2008 and 10/10/2008 I get back the correct date range BUT Read More

Views

56

Votes

0

Answers

27

August 16, 2011

ASP.NET convert date to insert into MS SQL Server

Hey guys, I have gotten stuck on a date conversion problem. I have a ASP.NET application written in VB I have a variable 'aDate' as string. The string is in the format of 20110204 01:30:00 (YY Read More

Views

64

Votes

0

Answers

7

August 18, 2011

Crystal to SSRS conversion

I have a crystal report I am trying to convert to SSRS and I was able to pull out the basic query, but now and having trouble with some of the "functions" or record selections.  Can the Read More

Views

79

Votes

0

Answers

1

August 15, 2011

Crustal Date format Conversion

I have a date formula called @EST (sample formula output: 11/11/2010   5:39:59PM) and I have a report column which is a Universe Object called {getdate} (sample field output: 2010-11-17 18:03:41. Read More

Views

59

Votes

0

Answers

6

August 16, 2011

ADODB.Connection?

<b>I am getting an error that says:</b> "Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Driver Manager] Data source name not found and no default Read More

Views

93

Votes

0

Answers

10

August 16, 2011

How to report datediff using Business Hours per day, not 24 hours per day?

My company is in the field service business.  Our typical hours are M-F, 8-5.  We target a 4 hour response time.  I need to calcualate the time difference between "Assign Time&quot Read More

Views

53

Votes

0

Answers

3

August 17, 2011

datetime problem

Again another problem with datetime in sql server; Im trying to simply INSERT a date into a datetime field in SQL Server, see code below: CurDate = CDate(Now()) Cnn.Execute "INSERT INTO tblTEST ( Read More

Views

0

Votes

0

Answers

17

August 18, 2011

ERROR Microsoft OLE DB Provider for ODBC Drivers (0x80040E57)

Hi,  I am using a oracle RDB casue i change from a sql database but i cant get the date to be right for the page to display. I get this error below. Error Type: Microsoft OLE DB Provider for ODBC Read More

Views

64

Votes

0

Answers

33

August 16, 2011

This is the very first question

Hello guys! This is the first post in this site Read More

Views

2k

Votes

4

Answers

2

January 08, 2016

report values not showing up.

I have a crystal report which I pass a DataSet to using VB.NET. The report was working fine, but then I make some changes to the import query and now nothing shows up in the crystal report. Crystal do Read More

Views

1k

Votes

0

Answers

12

September 06, 2005

Event log doesn't overwrite as needed in SP4

Hello All, After upgrading from SP3 to SP4 on several Windows 2000 PRO machines everything looked fine, but from time to time applications can't write to the event log. When I try to view the appl Read More

Views

1k

Votes

0

Answers

9

May 11, 2003

"Failed to self-register XYZ.dll"

Hi there, I wrote a OLE-automation-server DLL in VB4.0. I use Installshield Express to install it as part of my program on the target computers. Now on some computers I get the message "Failed to Read More

Views

8k

Votes

0

Answers

2

November 08, 1998