Date Query question

I am trying to access one of our SQL student tables by the change_date field. What is the proper way to get results when looking for a change_date >= "9/25/2006".

The field data looks like this: 9/25/2006 8:15:02 AM

Thx

The answer depends on how you are accessing the database. What application and/or API are you using? And what difficulties are you encountering?|||

I'm using the the query option in SQL Server Enterprise Manager. I'm getting no results when I know the data is there.

Thx

|||

Your change_date is most likely a Date/Time data type. In which case your sql string will look like this:

change_date >= #9/25/2006#

Whenever your querying data from a table, you need to think about what type of data of the fields you want to filter you search on.

If it's a date, time, or both, your gonna wrap your data within numeric/lbs. signs (Shift-3).

ex: date >= #1/1/2000#

If it's a string, your gonna wrap your data in single quotes.

ex: string_value = 'my value'

If it's a integer, you will not need to wrap the data in anything.

ex: integer_value = 100

|||

In order to retreive data from a table, where the data contains a time component other than midnight, you will have to accomodate the time issue in your search criteria.

One of the best methods is the following:

WHERE ( Change_Date >= '2006/09/25'
AND Change_Date < '2006/09/26'

This 'brackets' the day from midnight the 25th until just before midnight the 26th. Therefore the time of day is immaterial -all times during the day will be retreived. And it will use any indexing that may be available for Change_Date.

|||

AJ,

You forgot to remove your Access Developer brain and insert the SQL Server brain this morning. (Long holidays do that to us. )

T-SQL requires single quotes around the datetime values, whereas Access uses the pound sign (#).

|||

Arnie,

Works great, thanks.

Date Query

Good Morning to all

I wrote a query to access some data from sql server ,my query is as follows

strsel = "select * from schedule where sector_id='" & str_sec_id & "' And dep_date = " & bb & ""

bb is the date transferred from the other module

i want to check the dep_date as short date format.because bb is in short date format.

in the above checking i am not getting the query result

please look on this code

Regards

Unni

what you are passing in bb, if bb is datetime variable just do this bb.toshortDateString().

furthermore, try Response.write(strsel). so that you can get the actualy query on browser then try to run it in sql see what u get

cheers

|||

unnik_pulikkal:

strsel = "select * from schedule where sector_id='" & str_sec_id & "' And dep_date = " & bb & ""

Also it looks like you are forgetting the single quotes around your date field:

"select * from schedule where selector_id = 3 And dep_date = 1/3/2007"will not give you the results that you want

"select * from schedule where selector_id = 3 And dep_date = '1/3/2007'"will give you the results you want

Peace,

Date Parsing with DateValue Equivalent

Thanks in Advance,
MS Access has a flexible function called DateValue that will convert a
valid string into a date. Does anyone know a method of parsing ntext
values to detect and convert this strings into dates? The ntext values
might look something like this:
Established in 1974.
Created in Aug 1986 and dedicated on September 29th, 1986.
Abolished January 1, 1957. Reenacted on May 15, 1978, and transferred
functions on March 11, 1981 by executive order.
I suspect a combination of patindex search and other functions will do
the trick.
Mark
Napa, CAIf you are using SQL Server 2005, you could write a User Defined Function in
a .NET language that could do this type of parsing for you.
Hope this helps!
Chuck Heinzelman
MCSD, MCDBA
I support the Professional Association for SQL Server (www.sqlpass.org)
This posting is not an endoresment of any product.
Information is provided as-is, and carries no warranties - either express or
implied.
Please respond in newsgroups only.
<xxxdbaxxx@.gmail.com> wrote in message
news:1145893343.427405.182460@.v46g2000cwv.googlegroups.com...
> Thanks in Advance,
> MS Access has a flexible function called DateValue that will convert a
> valid string into a date. Does anyone know a method of parsing ntext
> values to detect and convert this strings into dates? The ntext values
> might look something like this:
> Established in 1974.
> Created in Aug 1986 and dedicated on September 29th, 1986.
> Abolished January 1, 1957. Reenacted on May 15, 1978, and transferred
> functions on March 11, 1981 by executive order.
> I suspect a combination of patindex search and other functions will do
> the trick.
> Mark
> Napa, CA
>|||Well, we are still in SQL 2000 Chuck. Any other thoughts?

Date Parsing using T-SQL

I can do this using MS Access 2000 database code.
String called
DKEY: "199306 30"
Using MS Access to convert that to a date the following code works:
DateValue(Mid(Replace([dkey]," ",""),5,2) & "/" & Mid(Replace([dkey],"
",""),7,3) & "/" & Mid(Replace([dkey]," ",""),1,4))
Returns the date value: 6/30/1993
How is this done using T-SQL? Any help greatly appreciated!!!
RBollingerIf your datestring is always formatted in that fashion (ie,
"YYYYMM[spaces]dd") , then the easiest thing to do is to remove the
spaces and convert it to a date.
SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
HTH,
Stu
robboll wrote:
> I can do this using MS Access 2000 database code.
> String called
> DKEY: "199306 30"
> Using MS Access to convert that to a date the following code works:
> DateValue(Mid(Replace([dkey]," ",""),5,2) & "/" & Mid(Replace([dkey],"
> ",""),7,3) & "/" & Mid(Replace([dkey]," ",""),1,4))
> Returns the date value: 6/30/1993
> How is this done using T-SQL? Any help greatly appreciated!!!
> RBollinger|||Convert(datetime, Replace([dkey], ' ',''), 112)
Tom
"robboll" <robboll@.hotmail.com> wrote in message
news:1149630420.422200.35050@.u72g2000cwu.googlegroups.com...
>I can do this using MS Access 2000 database code.
> String called
> DKEY: "199306 30"
> Using MS Access to convert that to a date the following code works:
> DateValue(Mid(Replace([dkey]," ",""),5,2) & "/" & Mid(Replace([dkey],"
> ",""),7,3) & "/" & Mid(Replace([dkey]," ",""),1,4))
> Returns the date value: 6/30/1993
> How is this done using T-SQL? Any help greatly appreciated!!!
> RBollinger
>|||thanks!
Tom Cooper wrote:
> Convert(datetime, Replace([dkey], ' ',''), 112)
> Tom
> "robboll" <robboll@.hotmail.com> wrote in message
> news:1149630420.422200.35050@.u72g2000cwu.googlegroups.com...|||I get an error when you trying this. In Access you have to accout for
a - or / or . delimiter between dates. Does the same apply to T-SQL?
SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
Stu wrote:
> If your datestring is always formatted in that fashion (ie,
> "YYYYMM[spaces]dd") , then the easiest thing to do is to remove the
> spaces and convert it to a date.
> SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
> HTH,
> Stu
>
> robboll wrote:|||I get an error when trying this. In Access you have to accout for a -
or / or . delimiter between dates. Does the same apply to T-SQL?
Tom Cooper wrote:
> Convert(datetime, Replace([dkey], ' ',''), 112)
> Tom
> "robboll" <robboll@.hotmail.com> wrote in message
> news:1149630420.422200.35050@.u72g2000cwu.googlegroups.com...|||SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5, 2) + '/' +
SUBSTRING(dkey, 1, 4)
provides a readable format, but there are no leading zeros in the day
like there are in the month, and it's still a text string.
convert(smalldatetime, SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5,
2) + '/' + SUBSTRING(dkey, 1, 4)) doesn't seem to work
robboll wrote:
> I get an error when trying this. In Access you have to accout for a -
> or / or . delimiter between dates. Does the same apply to T-SQL?
> Tom Cooper wrote:|||Okay -- This works. Y'all got me on the right track. Thank you!
CONVERT (smalldatetime, SUBSTRING(dkey, 5, 2) + '/' +
LTRIM(SUBSTRING(dkey, 9, 3)) + '/' + SUBSTRING(dkey, 1, 4))
robboll wrote:
> SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5, 2) + '/' +
> SUBSTRING(dkey, 1, 4)
> provides a readable format, but there are no leading zeros in the day
> like there are in the month, and it's still a text string.
> convert(smalldatetime, SUBSTRING(dkey, 9, 3) + '/' + SUBSTRING(dkey, 5,
> 2) + '/' + SUBSTRING(dkey, 1, 4)) doesn't seem to work
>
> robboll wrote:|||What error did you get? In SQL, a format of 20060601 is preferred;
it's unambiguous, and the pattern you provided should have matched
that. The only thing I can think of is that there must be some other
delimiters besides spaces.
I see you found a solution, but was just curious.
robboll wrote:
> I get an error when you trying this. In Access you have to accout for
> a - or / or . delimiter between dates. Does the same apply to T-SQL?
> SELECT CONVERT(smalldatetime, REPLACE(DKEY, ' ', ''))
> Stu wrote:|||For some reason I think the problem had to do with an inconsistent
string value where the month had leading zeros and the days didn't. To
get around this I used an acceptable date delimiter "/". But you're
absolutely correct it will work with the string date in your example.
Unfortunately mine was 2006061.
Stu wrote:
> What error did you get? In SQL, a format of 20060601 is preferred;
> it's unambiguous, and the pattern you provided should have matched
> that. The only thing I can think of is that there must be some other
> delimiters besides spaces.
> I see you found a solution, but was just curious.
> robboll wrote:

Date function in DTS package

How can I make this work against an Access table through an ODBC dsn in my DTS package? I need to subtract 1 day from the current date.

Tried this, which is incomplete of course, (need to subtract the 1 day):

WHERE (TTDateTimeIn >= DATEDIFF(dd, 1, { fn CURDATE() }))

Got this error:
[Microsoft][ODBC Microsoft Access Driver] Too few Parameters. Expected 1.I believe the function you should be using is DATEADD, not DATEDIFF. And you will need to enclose that dd in single quotes.

Terri|||That's correct, I finally figured it out. What added to my troubles was needing to pull yesterdays data after midnight, but I got it.

(TTDateTimeIn >= DATEADD('y', - 1, { fn CURDATE() }))

It was painful building this DTS package using a DSN connection to get to locked MS Access tables.

Thanks.

Date formatting - Really newbie question

I have a unbelievably stupid problem that I can't figure out. My table is
imported from access and contains a column with data type datetime. I want
to be able to sum the data (which is a door counter for our store) to show
me all of the traffic for the day, then display the date as 1/1/2007 instead
of 1/1/2007 12:00:00 PM. I have tried this... (which is how I interpret the
BOL help on this topic)...
convert(smalldatetime, [Date], 101)
No matter what I put in the style portion of the select statement it has no
impact on my output format. I suppose I could convert this to a varchar and
trim the results but that seems to be overkill (in addition to beiing a poor
solution).
Solved this. If anyone else is as inexperienced as I am and has this
problem, you can use the following method to change the output of the
datetime in this method.
Select CONVERT(varchar(20), [Date] as DT
will change this
1/30/2007 10:00:00
to this
1/30/2007
"Chuck G." wrote:

> I have a unbelievably stupid problem that I can't figure out. My table is
> imported from access and contains a column with data type datetime. I want
> to be able to sum the data (which is a door counter for our store) to show
> me all of the traffic for the day, then display the date as 1/1/2007 instead
> of 1/1/2007 12:00:00 PM. I have tried this... (which is how I interpret the
> BOL help on this topic)...
> convert(smalldatetime, [Date], 101)
> No matter what I put in the style portion of the select statement it has no
> impact on my output format. I suppose I could convert this to a varchar and
> trim the results but that seems to be overkill (in addition to beiing a poor
> solution).
>
|||I don't quite see how that works (20 is too long, and the is only one
parenthesis). To
convert a datetime value to the same date at midnight, this is one solution:
dateadd(day,datediff(day,0,[Date]),0)
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Chuck G. wrote:
[vbcol=seagreen]
>Solved this. If anyone else is as inexperienced as I am and has this
>problem, you can use the following method to change the output of the
>datetime in this method.
>Select CONVERT(varchar(20), [Date] as DT
>will change this
>1/30/2007 10:00:00
>to this
>1/30/2007
>"Chuck G." wrote:
>

Date Format in SQL

I have a question and any help would be greatly appreciated. I'm failry new to MS SQL, though i have been doing Access for sometime and am familiar with its SQL. I'm updating a database to MSSQL and am creating some views off of some old queries but the syntax has changed. Here is the Access SQL:

PARAMETERS Month Short;
SELECT tblrec.HCNumber, tblrec.LocationID, Sum(tblrec.Recvalue) AS RecValue, tblrec.use, tblrec.Unit, Format([Date],"m") AS Month
FROM tblrec
GROUP BY tblrec.HCNumber, tblrec.LocationID, tblrec.use, tblrec.Unit, Format([Date],"m")
HAVING (((Format([Date],"m"))=Month([month])));

The portion that I am having a problem with is the FORMAT statement to change the date column so it only displays the month (1-12) in the result pane. Any help would be greatly appreciated.

THanks!!format([date], "m") = datepart(month, current_timestamp)|||datepart(month, [date])

Be very careful when using keywords as names for objects in sql - I strongly suggest not using keywords.

Date Format

Hi All,
I am new to T-SQL and SQL 2005.
I would like to use this command that I used to with Access.
SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
"#,###.00") AS orderQuantity
FROM myTable
How can I do those thing in T-SQL?
Thank you in Advance
Regards,
Gun
See my reply in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gunawan" <jgun98.milis@.gmail.com> wrote in message news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>

Date Format

Hi All,
I am new to T-SQL and SQL 2005.
I would like to use this command that I used to with Access.
SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
"#,###.00") AS orderQuantity
FROM myTable
How can I do those thing in T-SQL?
Thank you in Advance
Regards,
GunSee my reply in .programming. Please don't multi-post.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gunawan" <jgun98.milis@.gmail.com> wrote in message news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>|||Hi
Read more on convert/cast functions in sql bol
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Gunawan" <jgun98.milis@.gmail.com> wrote in message
news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>

Date format

I'm trying to build a file-name variable that begins with a 'date'.

For example, I need to build logic in a T-SQL script that will access a file called 08032006abc123, with '08032006' being a representation of the date 8/3/2006.

Is there a quick/easy way to create this string, based on the date 8/3/2006? (would be nice if there was a date function that would zero-fill)

If you can use the date format YYYYMMDD or YYMMDD you can use CONVERT to get the encoded date ( http://msdn2.microsoft.com/en-us/library/ms187928.aspx ) else you can always do this and then rearrange the out string using the SUBSTR|||

The following syntax will return the date in the manner you are looking for:

DECLARE @.date datetime

SET @.date = GETDATE()

select replace(convert(varchar(10), @.date, 101), '/', '')

Results: 09082006

Date Format

Hi All,
I am new to T-SQL and SQL 2005.
I would like to use this command that I used to with Access.
SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
"#,###.00") AS orderQuantity
FROM myTable
How can I do those thing in T-SQL?
Thank you in Advance
Regards,
GunSee my reply in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gunawan" <jgun98.milis@.gmail.com> wrote in message news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.g
bl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>|||Hi
Read more on convert/cast functions in sql bol
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Gunawan" <jgun98.milis@.gmail.com> wrote in message
news:OVL3f5FnHHA.4316@.TK2MSFTNGP06.phx.gbl...
> Hi All,
> I am new to T-SQL and SQL 2005.
> I would like to use this command that I used to with Access.
> SELECT orderDate, format(orderDate,"mmdd") AS dateCode, format(Quantity,
> "#,###.00") AS orderQuantity
> FROM myTable
> How can I do those thing in T-SQL?
> Thank you in Advance
> Regards,
> Gun
>

Date expression in Access

I'm pretty new to this whole "programming" game. The date field I'm using is [date]. I need to create two queries. One will pull all records year-to-date this year. The other should pull all records year-to-date last year. I'm sure there is an expression that accomplishes this, without having to type in parameters each time I execute the query. Examples, please.datediff(yy, [YourDateValue], getdate())

...will give the difference in the years between your data value and the current date.

Value 0 means YourDateValue is current year-to-date.
Value 1 means YourDateValue is prior year-to-date.
.
.
.
etc

blindman

Date Concatenation

Working with an archaic mess of Access dbs, and am trying to convert to a single SQL Server 2005 db.

A couple of places in the original monstrosity I have the time and date in separate columns, like the following example:

[Ticket Date] [Ticket Time]
12/1/99 0845
12/2/01 0932

One problem is the two columns can be of different data types, like a date col and a text time. If anyone knows how to bring these into a single column using SSIS, I would love to know since I have been battling with it for a while.

Use the Derived Column component. Cast them as strings and then its just a string a manipulation problem.

-Jamie

DATE AND HOUR

Hello people,

I'm migrating an application from asp to asp.net and access to ms sql 2005. On the old db, there's a table with a column to store date and a column to store time. Ms sql only works with date and time together, right? I created a query to get the time from time column and update the date column inserting the time. I did something like this:

UPDATE S_ACC_MONEY
SET Date_Oper = CONVERT(varchar, Date_Oper, 101) + ' ' + CONVERT(varchar, Time_Oper, 108)

Does anyone knows a better way to do that? Any other comments?

Thanks!

you might also want to do a final conversion to datetime as in:

Date_Oper =CONVERT(datetime,CONVERT(varchar, Date_Oper, 101) + ' ' + CONVERT(varchar, Time_Oper, 108))

Date / Time types

Hi, I'be just started to import data into sqlserver from ms access, and it automatically stores my dates as smallint length 4. Is this ok? For display purposes I want dd/mm/yyyy. Will 4 bytes store this?I generally recommend DATETIME unless I know that the values can be stored properly in a SMALLDATETIME column. See the Using Date and Time data (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_7uzy.asp) discussion in MSDN for more details.

-PatP