?
Solved

Convert Access queries T-SQL

Posted on 2003-02-20
10
Medium Priority
?
1,786 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
[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
  • 2
  • 2
  • 2
  • +2
10 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
Independent Software Vendors: 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!

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

801 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