comparing dates that have difference formats

Hi using sql 2005. I have a datetime field that has a value like '2010-01-13 
15:29:48.060'. I would use a where condition that does not include 
milliseconds. That is, I want the where condition to read something like...

convert(varchar(20),dateAt,??)='2010-01-13 
15:29:48'

I have not yet got my head around using dates in select statements. So I'm 
hoping you can help me with this.

Any ideas or recommendations appreciated. :-)

Many thanks,
Jonathan
0
Utf
1/14/2010 1:04:01 AM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
524 Views

Similar Articles

[PageSpeed] 24

Try replacing ?? with 20 or 120.  See here for a list of CONVERT function 
datetime formats.

-- 
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption" 
(http://www.apress.com/book/view/1430224649)
----------------

"Jonathan" <Jonathan@discussions.microsoft.com> wrote in message 
news:82F58864-E33F-406D-B125-136DA33446E3@microsoft.com...
> Hi using sql 2005. I have a datetime field that has a value like 
> '2010-01-13
> 15:29:48.060'. I would use a where condition that does not include
> milliseconds. That is, I want the where condition to read something 
> like...
>
> convert(varchar(20),dateAt,??)='2010-01-13
> 15:29:48'
>
> I have not yet got my head around using dates in select statements. So I'm
> hoping you can help me with this.
>
> Any ideas or recommendations appreciated. :-)
>
> Many thanks,
> Jonathan 

0
Michael
1/14/2010 1:24:13 AM
Oops, forgot to post the link: 
http://msdn.microsoft.com/en-us/library/ms187928.aspx

-- 
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption" 
(http://www.apress.com/book/view/1430224649)
----------------

"Michael Coles" <admin@geocodenet.com> wrote in message 
news:0DB376AA-71E7-4170-9556-CDB00B210C6B@microsoft.com...
> Try replacing ?? with 20 or 120.  See here for a list of CONVERT function 
> datetime formats.
>
> -- 
> Thanks
>
> Michael Coles
> SQL Server MVP
> Author, "Expert SQL Server 2008 Encryption" 
> (http://www.apress.com/book/view/1430224649)
> ----------------
>
> "Jonathan" <Jonathan@discussions.microsoft.com> wrote in message 
> news:82F58864-E33F-406D-B125-136DA33446E3@microsoft.com...
>> Hi using sql 2005. I have a datetime field that has a value like 
>> '2010-01-13
>> 15:29:48.060'. I would use a where condition that does not include
>> milliseconds. That is, I want the where condition to read something 
>> like...
>>
>> convert(varchar(20),dateAt,??)='2010-01-13
>> 15:29:48'
>>
>> I have not yet got my head around using dates in select statements. So 
>> I'm
>> hoping you can help me with this.
>>
>> Any ideas or recommendations appreciated. :-)
>>
>> Many thanks,
>> Jonathan
> 

0
Michael
1/14/2010 3:16:39 AM
You you really use
" WHERE dateAt  >= <YourDate> and DateAt < dateadd(dd,1,<YourDate>) "

to allow for correct index usage.


Dave Ballantyne
http://sqlblogcasts.com/blogs/sqlandthelike/

Jonathan wrote:
> Hi using sql 2005. I have a datetime field that has a value like '2010-01-13 
> 15:29:48.060'. I would use a where condition that does not include 
> milliseconds. That is, I want the where condition to read something like...
> 
> convert(varchar(20),dateAt,??)='2010-01-13 
> 15:29:48'
> 
> I have not yet got my head around using dates in select statements. So I'm 
> hoping you can help me with this.
> 
> Any ideas or recommendations appreciated. :-)
> 
> Many thanks,
> Jonathan
0
Dave
1/14/2010 9:51:55 AM
Am 14.01.2010 02:04, schrieb Jonathan:
> Hi using sql 2005. I have a datetime field that has a value like '2010-01-13
> 15:29:48.060'. I would use a where condition that does not include
> milliseconds. That is, I want the where condition to read something like...
>
> convert(varchar(20),dateAt,??)='2010-01-13
> 15:29:48'

convert (varchar(20), dateAt, 120)
0
Lutz
1/14/2010 10:42:19 AM
Reply:

Similar Artilces:

compare
Hello everyone, I have been reading this group for a while, however I am quite a rookie in using of excel. I would appreciate if someone could help with this issue. I quite often have to compare two sheets (from different workbooks-files, but with the same sheet name). Calculations are thus updated time by time and I need to check where were the main differences. Cells contain both values and formulas. I have found through this newsgroup nice add-ins of Myrna Larson and Bill Manville, and Rob Bruce. However I would need the macros to highlight only significant differences (let's say fro...

How do I print 4 different postcards on the same page?
I am trying to print a mail merged document of postcards. I only need one copy of each post card. The only options I can find are to either print four of the same postcard or one per page. I would like to print four different post cards on one page. Does anyone have any idea how I can do this. You're doing a mail merge for four postcards? "EMCL" <EMCL@discussions.microsoft.com> wrote in message = news:BA4A5215-CA5F-4144-941C-295770B06219@microsoft.com... > I am trying to print a mail merged document of postcards. I only need = one=20 > copy of each post car...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

Great Plains Date and Time
What time is Great Plains using in Service Call Time sensitive field? if we have different time zones and Each Time Zones is using a specifice Server, whihc date/time is used by Great Plains. Is it the User Profile time or the SQL Server System time. Thanks ems When a new service call is created, the time zone will be defaulted to the base time zone stored in the SVC_Master_Defaults table (SVC00998) and defined in the Service Setup window (Microsoft Dynamics GP > Tools > Setup > Project > Service). However, when the customer is selected, the Service Call will change time ...

auto date not working
I have a form where I want the date that the information was entered to be displayed automatically. I have the text box disabled so that no one can change the data. In the text box I have =Date() which works fine but when I go back to that record the next day, the date changes to that day's date. Please help!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1 Set the Default Value of the field in the table to =Date(), rather than the Default Value of the control. -- Doug Steele, Microsoft Access MVP http...

On POS (not RMS) Adjusting Time Clock Defaults to Today's Date #2
When an employee negelects to time out and we make adjustments on a subsequent day, the Time Clock display will default to today's date even though the correct date is selected in the Employee's Time Clock window. You have to select Save and Close, then reopen the Time Clock event, then adjust the time out time to the correct date. Anybody else notice this bug? M Kalmus Dogtooth Coffee Company ...

Picture Formats After Reinstall In Publisher
I had to reinstall Publisher and now that I have, the pictures formats that I use on my computer are not availabe in the "List file types" when I try to double click and insert an image after creating an image box. I need .jpg. gif and .bmp but only show metafiles, wordperfect graphic and pc paintbrush import. Even under the "show all file types" heading, I cannot insert .jpg, ..bmp, or .gif files types. What should I do. Could be your filters are missing or were corrupt and the re-install didn't overwrite them. Rename the graphic filters and drag new ones from ...

Counting occurances of same data in different fields
I am creating a conference registration program that allows registrants to select up to three events to participate in. Is there an easy way to count the total number of registations for each event that occurs in the three selection fields? On Mar 16, 2:54 pm, Fred Morris <FredMor...@discussions.microsoft.com>wrote:> I am creating a conference registration program that allows registrants to> select up to three events to participate in. Is there an easy way to count> the total number of registations for each event that occurs in the three> selection fields?I gather you hav...

2 workbooks
I have got 2 workbooks with a list of names. I need to find out if a name appears in both books. One of the workbooks came from a different source and the other one is a report I ran from our database. I need to find out whether the workbook from out of the company has got any of our own names on it. I am using Microsoft Excel 97 and am fairly new at this so please be gentle. One way I did think was to combine the two workbooks into one and find the duplicates but thought there may be another way. Thank you. Hi "queen on", Assume that In book1 the names are in column A, s...

Format all comments made now and in future to be size 14
How do you format all comments made, in all workbooks, to be 14 Times New Roman? How do you do it before, not after... Check out the below by Debra Dalgleish http://www.contextures.com/xlcomments02.html#Default If this post helps click Yes --------------- Jacob Skaria "annc5411" wrote: > How do you format all comments made, in all workbooks, to be 14 Times New > Roman? How do you do it before, not after... ...

Is Secure Email and Rich Text Format mutually exclusive???
I have failed getting Outlook 2000 and Outlook 2002 communicate using certificate based secure email and Rich Text formatted messages. They are either converted to plaintext (OL 2000) or to HTML (OL 2002). Is there a setting somewhere I have missed or is this the way it actually is supposed to work? We really need to retain the Rich Text formatting.... /Bo ...

Comparing
I have two columns of numbers column A could be up to 3500 rows. Column B may be more or less than 3500 rows. What I'm trying to do is see if any number in columns "B" appears anywhere in Column "A", and if so we can just highlight it in both columns. Actually I would want to be able to sort by highlighted. so maybe add someway to sort by matched or unmatched. I hope this makes sense. In column C put... =IF(ISNA(VLOOKUP($A1,$B:$B,1,FALSE)),"Not in B","In B") In column D put... =IF(ISNA(VLOOKUP($B1,$A:$A,1,FALSE)),"Not in ...

Notes table and IndirectDataInput / Date stamp in Memo field
I am trying out the previous suggestions for a solution regarding time/date stamp in a memo field. Both are very interesting. In the case of a separate Notes Table…how do I transfer the current memo field to the memo field record in the new Notes table? My database resides 900 records with memo fields… Thankful for advice! You will need a foreign key in the Notes table that tells you, for every row in the notes table, which is the row in the main table that the note belongs to. You need to extract both the notes and the PK of the record for each note. Use a query to do...

Format a social security in an Access report
I do not know the syntax to get a field to print out in the correct format. I have a social security number that is being entered into a table using the input mask. It is printing out without the dashes. EG (123446123). I can't remember the correct syntax to use. Thanks Format([SSN],"@@@ @@ @@@@") Or Format([SSN],"@@@-@@-@@@@") If you are using the format property of the control then use the following in the property @@@ @@ @@@@ or @@@-@@-@@@@ -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University o...

Task Due Date
Is there anyway to preset the task to have "today" as the due date by default? -- Guy Lapierre Forefront Business Solutions http://www.forefrontbusinesssolutions.com Not that I am aware of, no. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, Guy Lapierre asked: | Is there anyway to preset the task to have "today" as the due date by | default? ...

SUMPRODUCT Date Ranges
Hi, I need to note a '0' or '1' within each of the monthly cells in a table to show resource secured within a given month (the date ranging from the start and finish date). So, if the START date is A1 and the END date is B1 and I have a cell for each month, i.e. C1 = Jan, D1 = Feb and so on till Dec. As an example, the start date is 06/02/10 and the end date is 08/08/10, how can I show "1" against the Feb to August cells and all the other cells where they don't fall into the month as "0", i.e. Jan as "0"? Many thanks in advanc...

I need to compare to columns and indicate the matches in another
am wanting to compare 2 columns for exact matching. If there are two matching items I want to be able to say "A match" in a chosen cell for all the ones that match. Column C will be retrived in an random order... so how would I write the formula for that????? Example: A B C 45time 11tune A match 11tune 89time 47doog 43jkjkj A match 123ABC 123ABC If possible include how to highlight the ones that make as another option. Thanks Try something like this: For a value list in B1:B5 and a ...

Help Please- How to offset Date to correct column for Ageing
Hi All, I have a stock ageing sheet for finished goods, which uses a vlookup table, on entry of the Product code (last 3 Digits), this populates the next three columns with description, FG cost and Quantity of product. the quantity of product is entered again manually into the appropriate column, there are five columns these are banded <3 Months 0%, 3 to 6 Months 25%, 6 to 9 months 50%, 9 to 12 Months 75% and > 1 Year 100%, the percentage is liability provision. What I would like to do is on entry of the product code as above the quantity is automatically inserted into the correc...

Dates changed on Copy Cell
I am now using Excel 2007 and keep finding that the year in the date is changed when I copy from one cell to another in another spreadsheet. I start with 9/12/2008 17:00 (formated as a custom format) and it is copied in as 10/12/2012 17:00 (same format). Can anyone help? Thanks Jim > changed when I copy from one cell to another in another spreadsheet. I assume that you mean Workbook not Worksheet in the same Workbook. You have the second Workbook set to the 1904 date system. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinato...

Determining the last business date
Is there any way (at 5pm in the continental USA) I can (fairly) conclusively determine the most recent date on which the stock market was open? This question comes from the following: If I have my VBScript hop over to http://my.msn.com/ then I can take a look at a fixed set of stock quotes (provided I have set them up). However, I don't see the date shown in any way. Now, if I could guarantee that the script would be run every day, then I could do some tricks (like checking whether all the quotes are the same as the prior accepted set - if so, I can assume the next date). ...

Help formating
I have cells which can accpet percentages and currency values my questions is when i enter a value <1 i want the cell to format as % if the value is >1 then i want it to be currency. can some one help me out i cant do this using condityional formating. any one have ideas ??? Regards, Alexandre Use a custom format instead: Format/Cell/Number/Custom [<1]0.00%;$#,##0.00 You don't say what should happen if the value is =1. I assumed currency format. In article <1122487381.944331.179000@g43g2000cwa.googlegroups.com>, "Alexandre Brisebois (www.pointnetsolutio...

Special converting of string date&time to date format
Hi there, Working with a file and all my dates and times appear as one long text string shown below that I would like to convert to date and time format so that I can manipulate the data anyway I like. Data appears as follows: .. cell A1: 10/1/2011 2:20:00 PM cell A2: 1/18/2011 2:20:00 AM I have tried many different ways and sometimes it works. The problem is when the date and month are not 2 digits. As you see the date or the month can be 1 or 2 digits. How do I convert it so that it works no matter what.. this works only when the month and date are both 2 digits =DATE(...

Advice on comparing data sets
Hi, Can anyone advise on how I can compare data on 1 sheet with dat compared on another. Example attached. I want to be able to show that whenever the UK is shown (can appea multiple times) on this sheet it checks on sheet2 and enter th corresponding band value in this case for the UK (show in cells B6, B8 will show band A SHEET1 Ref Country Band 1 UK 2 Germany 3 UK 4 France 5 Italy SHEET2 Country Band UK A GERMANY B ITALY B FRANCE D SWEDEN D DENMARK E As always thanks for your help. Simo +------------------------------------------------------------------- |Filename: ...

what does it mean when the date is nothing but #######?
I am using the invoice template and all of a sudden today, it is not showing the date. I have tried to delete it and type in the date again, but it isn't doing anything. How do I fix it to show the date? It's a negative date. Go to tools|options|calculation and check the 1904 date system. Hopefully this will cure the problem. -- Ian -- "Ninabean" <Ninabean@discussions.microsoft.com> wrote in message news:C6445E30-09DE-49EB-B767-7720474B0FBD@microsoft.com... >I am using the invoice template and all of a sudden today, it is not >showing > the date. I have...

How do I chart date ranges with varying start and finish dates?
I want to create a chart that on one axis will show date ranges that have varying start and finish dates. The other axis is a list of project names. Example: Project #1, starts 5/1/05, ends 10/1/05; Project #2 starts 3/5/05, ends 2/2/06, etc. I want a bar chart that I can sort. Any help is very appreciated. : ) It sounds like you need to create a Gantt chart. Here are some resources that might help: http://www.peltiertech.com/Excel/Charts/GanttLinks.html http://www.peltiertech.com/Excel/Charts/GanttChart.html http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343 ----...