Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9796
  • Last Modified:

Cdate function with MS-SQL Server

what do I need to install to be able to use this function  ?

When i install ms-access this function is installed (it is NOT an integral part of ms-access...)
0
guyss
Asked:
guyss
  • 4
  • 4
  • 3
  • +3
1 Solution
 
Victor SpiridonovCommented:
There is no CDate function in MSSQL, you should use CONVERT function. It has the same functionality as all C..(CInt,Cdate,Cdbl,etc) functons in Access.
0
 
guyssAuthor Commented:
well, CDate isn't a part of access either, but it works....

I would like it to install, since
I want code that runs on access as well as SQL server...
0
 
paaskyCommented:
Hello guyss,

CDate() function comes with VBA library. If you open module window in Access then choose from menu View | Object browser and then select VBA library -> Conversion you'll see all VBA conversion functions - including CDate(). These functions can be used with Access database, and they work in Access Query if you link other ODBC database tables (such as SQL Server) into your Access database project. However if you use Pass-Thru queries or try this for eg. with Oracle Sql*Plus or SQL Server (I don't know what's relative SQL Editor), those functions are not available.

I think spiridonov is right about you should use CONVERT. With Oracle, I need to use TO_DATE() function, for eg.

SELECT TO_DATE('01-JAN-2000','dd-mon-yyyy') FROM table1;

With SQL Server it goes something like that:

SELECT CONVERT(DATETIME, '02/25/2000') FROM table1;

And MS Access:
SELECT CDate('02/25/2000') FROM table1;

Regards,
Paasky
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Victor SpiridonovCommented:
If you want to use it inside SQL Statements, the only option is CONVERT.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
wisper:
SQL7 has also CAST, which is similar to CONVERT
0
 
guyssAuthor Commented:
so there is no way to write multi platform code using dates ???
0
 
Victor SpiridonovCommented:
You can store dates as char, but then you will have to handle date convertion in your client code.
0
 
paaskyCommented:
guyss, from where are you accessing database? From MS Access, VB?

I've defined both databases to use same date mask and then updated them using ADO or DAO Execute method and I didn't need to convert the string to date format using any database specific date format function.

For eg. with ADO:

....
sql = "insert into mytable (datefield) values ('05/21/2000')"
Set rst = Server.CreateObject ("ADODB.Recordset")
rst.Open Sql, conn

That has worked fine with both databases which I have accessed from ASP pages.

Regards,
Paasky
0
 
paaskyCommented:
(datefield is datetime type in both databases)
0
 
Jeremy_DCommented:
>> so there is no way to write multi platform code using dates ???

Sure there is. Store your queries on the DBMS as views or stored procs and connect your clients using ODBC. Should work with almost any DBMS system.
0
 
guyssAuthor Commented:
i access my db from asp pages.

paasky, inserts do work but try to select with a where statement and a date...

jeremy

I work with access for development ease
of use, it would be pointles to store
everything as stored procedures...
0
 
Jeremy_DCommented:
>> it would be pointles to store
everything as stored procedures

Depends on what you want. It's either using Access as a RAD tool (for which it's quite good), thus limiting yourself to MS products (although you can link any ODBC source in Access, you're bound to find compatibility problems like these with non-MS products), or spending some more time on development and make your system more portable to other platforms.

As far as your date-problem goes. I've always found that formatting my date-values to the ISO standard format (yyyymmdd hh:mm:ss) on the client and then putting them in your SQL as a string (most DBMS system support implicit conversion from string to date for some formats) works quite well. You might want to try that.
0
 
paaskyCommented:
guyss,

my select clauses work too... but the date format in string must be exactly same than in database. You could use DatePart() function in ASP to rebuild the string to same date format:

MyDate = DatePart("M", MyDate) & "-" & DatePart("D", MyDate) & "-" & DatePart("YYYY", MyDate)

sql = "select field1 from table1 where datefield > '" & MyDate & "'"

Another idea to solve this problem is that you could have session variable that you assign value in first page (which is different depending the database the server is using) which defines the date format function:

With Access Database:

Session("DateFormatFunction") = "CDate("

with SQL Server

Session("DateFormatFunction") = "CONVERT(DATETIME,"

With Oracle:

Session("DateFormatFunction") = "TO_DATE("

In pages which use database:
....
sql = "select field1 from table1 where datefield > " & Session("DateFormatFunction") & "'" & MyDate & "')"
....

How does this sound to you?

Regards,
Paasky
0
 
guyssAuthor Commented:
yeh, I have already done this about 4 days ago, I just thought i'd get a better way ...

10x all, anyway...
0
 
dfdf sdfdfCommented:
not working
mydate >=convert(date,'01.01.2016')
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now