Solved

Cdate function with MS-SQL Server

Posted on 2000-05-14
15
8,583 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Expert Comment

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

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
 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

791 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