Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Convert Access queries T-SQL

Posted on 2003-02-20
10
Medium Priority
?
1,792 Views
Last Modified: 2007-12-19
I am trying to convert a date type field form text to date/Time or from nvarchar to datetime.

The field in Access looks like this:

Date: CVDate(Left(BOL_Hartford_Raw!Date,2)+"/"+Mid(BOL_Hartford_Raw!Date,3,2)+"/"+Right(BOL_Hartford_Raw!Date,2))

In T-SQL I have something like this
LEFT (Date, 2) + ' /' + LEFT (Date, 4) + ' /' + RIGHT (Date, 2)
First SQL tells me that there is no MID function and second I can not convert from text to datetime and I can not get the middle 2 number  to show like: 02/20/03



0
Comment
Question by:petroleo
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 5

Expert Comment

by:funke
ID: 7988055
strDt = cstr(format(BOL_Hartford_Raw, "mm/dd/yyyy"))


or change the format to whatever you like

change CVDate(...) to CDate(...)
0
 

Author Comment

by:petroleo
ID: 7988119
CVDate is not a recognize function in SQL
This is what I try

CVDate(Date, [mm/dd/yyyy])

Also cstr amd format are not recognize either by SQL
0
 
LVL 5

Expert Comment

by:funke
ID: 7988706
sorry - thougth you were exporting from Access - don't know jack about TSQL
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Expert Comment

by:marcdurham
ID: 7988752
Try this, the substring(expression,start,length) is just like the mid function in vb.

convert( datetime, substring(BOL_Hartford_Raw!Date,1,2) + '/' + substring(BOL_Hartford_Raw!Date,3,2) + '/' + substring(BOL_Hartford_Raw!Date,5,2) ) AS Date

hope it helps.
-Marc-
0
 

Expert Comment

by:marcdurham
ID: 7988782
No wait, I'm sorry, don't use BOL_Hartford_Raw!Date
Use a dot instead of the bang: BOL_Hartford_Raw.Date

-Marc-
0
 
LVL 1

Expert Comment

by:DGordo63
ID: 7988817
In T-SQL you normally can place string values into date datatype fields but must do it in the correct format (yyyymmdd)

SUBSTRING function in T-SQL is the equivalent of the MID function in Access.

Example:  The date string is currently: 02/20/2003 and you want to insert that date into the Mydate field (which is a datetime datatype) in the 'table_name' table in SQL.

Here is the syntax:

INSERT INTO table_name(Mydate)

SELECT RIGHT(date_string_field_name, 4) +
SUBSTRING(date_string_field_name, 3, 2) +
LEFT(date_string_field_name, 2) AS MyDate
FROM Access_table;

HTH, DGordo63


0
 
LVL 1

Accepted Solution

by:
DGordo63 earned 200 total points
ID: 7988844
Correction:  Month should follow year first.. See below

**********************************************************
In T-SQL you normally can place string values into date datatype fields but must do it in the correct format (yyyymmdd)

SUBSTRING function in T-SQL is the equivalent of the MID function in Access.

Example:  The date string is currently: 02/20/2003 and you want to insert that date into the Mydate field (which is a datetime datatype) in the 'table_name' table in SQL.

Here is the syntax:

INSERT INTO table_name(Mydate)

SELECT RIGHT(date_string_field_name, 4) +
LEFT(date_string_field_name, 2) +
SUBSTRING(date_string_field_name, 3, 2) AS MyDate
FROM Access_table;

HTH, DGordo63
0
 
LVL 18

Expert Comment

by:1William
ID: 8707871
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to o63
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
1William
EE Cleanup Volunteer
0
 
LVL 18

Expert Comment

by:1William
ID: 8707872
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to DGordo63
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
1William
EE Cleanup Volunteer
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Implementing simple internal controls in the Microsoft Access application.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

580 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