Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

date column behaving strangely Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 10:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 9:32 AM
Points: 299, Visits: 1,195
hi,

i have a column that's treating dates in an odd way.

select filldate, datediff(dd, filldate, getdate()) as Diff
from j2551963
group by filldate
having datediff(dd, filldate, getdate()) < 13
order by filldate

filldate Diff
03/01/2013 12
03/02/2013 11
03/03/2013 10
03/04/2013 9
03/05/2013 8
03/06/2013 7
03/07/2013 6
03/08/2013 5


select filldate, count(*) as Records
from j2551963
where filldate like '03/%'
group by filldate
order by filldate

filldate Records
03/01/2013 2728
03/02/2013 1228
03/03/2013 908
03/04/2013 2525
03/05/2013 2497
03/06/2013 2153
03/07/2013 2086
03/08/2013 1726

select convert(varchar, filldate, 101)as FillDate, count(*) as Records
from j2551963
group by filldate
having datediff(dd, convert(varchar, filldate, 101), getdate()) < 13
order by filldate

FillDate Records
03/01/2013 2728
03/02/2013 1228
03/03/2013 908
03/04/2013 2525
03/05/2013 2497
03/06/2013 2153
03/07/2013 2086
03/08/2013 1726

here is column information:

id_Field Field_vartype Field_Varsize SourceName
FillDate nvarchar 64 FillDate

data was imported from excel files. i guess my question is: how come just a few columns are recognized by the datediff without a convert statement? is it an excel issue, or an import issue? i know that's a bit open ended without posting more information, but i'm curious if there's an answer before getting deeper into it.

thanks



Post #1430520
Posted Wednesday, March 13, 2013 11:31 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 22,511, Visits: 30,236
I can't figure out what your problem is here.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1430544
Posted Wednesday, March 13, 2013 12:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:24 PM
Points: 11,990, Visits: 11,007
Seems to me that the only real issue here is that you have a varchar(64) column that should be a datetime column.

As you said yourself, there is not much information here to go on. Perhaps if you post some details we can help.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1430576
Posted Wednesday, March 13, 2013 12:49 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 9:32 AM
Points: 299, Visits: 1,195
heh. no. i had a monumental brainfart.

i was looking at the 'diff' column like it was a count, not like it was showing the difference between the two dates.

sorry about that, and thanks for looking. as soon as this three week old lets me get some sleep, i'll have better questions.
Post #1430583
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse