Solved

Cdate function with MS-SQL Server

Posted on 2000-05-14
15
8,486 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 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
 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 shrink a transaction log file down to a reasonable size.

785 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