Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cdate function with MS-SQL Server

Posted on 2000-05-14
15
Medium Priority
?
9,471 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

730 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