Solved

Converting Code from Access to SQL -- Specifically DateSerial

Posted on 2006-10-26
2
299 Views
Last Modified: 2012-05-05
I am having trouble converting a bit of Access code that appears in my select statement as:

SELECT DateSerial(Year(dbo_TPB105_CHARGE_DETAIL!chg_srv_ts),Month(dbo_TPB105_CHARGE_DETAIL!chg_srv_ts),Day
(dbo_TPB105_CHARGE_DETAIL!chg_srv_ts)) AS cntdate

And also in the WHERE statement:

WHERE (((DateSerial(Year([dbo_TPB105_CHARGE_DETAIL]![chg_srv_ts]),Month([dbo_TPB105_CHARGE_DETAIL]![chg_srv_ts]),Day([dbo_TPB105_CHARGE_DETAIL]![chg_srv_ts])))>Date()-2 And (DateSerial(Year([dbo_TPB105_CHARGE_DETAIL]![chg_srv_ts]),Month([dbo_TPB105_CHARGE_DETAIL]![chg_srv_ts]),Day([dbo_TPB105_CHARGE_DETAIL]![chg_srv_ts])))<Date()))

What I am trying to accomplish is to obtain the records where the chg_srv_ts is yesterday (between 12:00 a.m. and 11:59 p.m.)

Can anyone help?

Thanks!



0
Comment
Question by:PHS_IT
[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 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 17813740
Hi PHS_IT,

SELECT CONVERT(datetime, CONVERT(varchar(12), dbo.TPB105_CHARGE_DETAIL.chg_srv_ts, 106)) AS cntdate

...

WHERE CONVERT(datetime, CONVERT(varchar(12), dbo.TPB105_CHARGE_DETAIL.chg_srv_ts, 106)) =
    (CONVERT(datetime, CONVERT(varchar(12), GETDATE(), 106)) - 1)

Regards,

Patrick
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17813746
SELECT CAST(CONVERT(nvarchar(10), chg_srv_ts,112) as datetime)  as cntdate
in the WHERE statement

WHERE chg_srv_ts BETWEEN dateadd(day,-1 ,CAST(CONVERT(nvarchar(10),getdate(),112) as datetime)) AND CAST(CONVERT(nvarchar(10),getdate(),112) as datetime)

the where will always return yesterdays data
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

624 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