Solved

Cdate function with MS-SQL Server

Posted on 2000-05-14
15
8,287 Views
Last Modified: 2016-09-17
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
Comment
Question by:guyss
  • 4
  • 4
  • 3
  • +3
15 Comments
 
LVL 7

Expert Comment

by:spiridonov
ID: 2809489
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
 
LVL 1

Author Comment

by:guyss
ID: 2809691
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
 
LVL 10

Expert Comment

by:paasky
ID: 2809790
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
 
LVL 7

Expert Comment

by:spiridonov
ID: 2809807
If you want to use it inside SQL Statements, the only option is CONVERT.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2810103
wisper:
SQL7 has also CAST, which is similar to CONVERT
0
 
LVL 1

Author Comment

by:guyss
ID: 2810150
so there is no way to write multi platform code using dates ???
0
 
LVL 7

Expert Comment

by:spiridonov
ID: 2810185
You can store dates as char, but then you will have to handle date convertion in your client code.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Expert Comment

by:paasky
ID: 2810715
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
 
LVL 10

Expert Comment

by:paasky
ID: 2810719
(datefield is datetime type in both databases)
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2811002
>> 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
 
LVL 1

Author Comment

by:guyss
ID: 2813154
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
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2813458
>> 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
 
LVL 10

Accepted Solution

by:
paasky earned 100 total points
ID: 2813773
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
 
LVL 1

Author Comment

by:guyss
ID: 2817162
yeh, I have already done this about 4 days ago, I just thought i'd get a better way ...

10x all, anyway...
0
 

Expert Comment

by:dfdf sdfdf
ID: 41802920
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now