datetime to database problems

Anti-Rich

Well-known member
Joined
Jul 1, 2006
Messages
325
Location
Perth, Australia
Programming Experience
1-3
hi all,

i have extreme frustration when dealing with datetime and smalldatetime data types in sql server. all i want to do is store a date (from a date time picker in vb.nete) in a table, using a short format (ie. dd/mm/yyyy). now the datetime always includes the time, which i dont want to use, i just want the date.

now the reason im getting so frustrated is i want to use a between clause to grab records according to certain dates supplied... i do NOT want it to even look at the time, just the date. please, somebody help me this is insanely frustrating...

this is my query

VB.NET:
[SIZE=2][COLOR=#0000ff]select[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'Item'[/COLOR][/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2]n[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Description[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]'Times Picked'[/COLOR][/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE]
[SIZE=2][COLOR=#808080]([/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]select [/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff]count[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]Qty[/SIZE][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][SIZE=2] tblWT [/SIZE][SIZE=2][COLOR=#0000ff]where[/COLOR][/SIZE][SIZE=2] ID[/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'000013388'[/COLOR][/SIZE]
[SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
[SIZE=2][COLOR=#808080],[/COLOR][/SIZE]
[SIZE=2][COLOR=#ff0000]'Volume Picked'[/COLOR][/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE]
[SIZE=2][COLOR=#808080]([/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]select [/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff]sum[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]Qty[/SIZE][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][SIZE=2] tblWT [/SIZE][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][SIZE=2] ID [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'000013388'[/COLOR][/SIZE]
[SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][SIZE=2] tblWT w[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] tblID n [/SIZE][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][SIZE=2] n[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]ID [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'000013388'[/COLOR][/SIZE][SIZE=2][COLOR=#808080]and[/COLOR][/SIZE][SIZE=2] w[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]wDate [/SIZE][SIZE=2][COLOR=#808080]between[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'29/10/2006'[/COLOR][/SIZE][SIZE=2][COLOR=#808080]and[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'2/11/2006'[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]group[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]by[/COLOR][/SIZE][SIZE=2] n[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Description [/SIZE][SIZE=2][COLOR=#0000ff]ORDER[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]BY[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'Times Picked'[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Asc[/COLOR][/SIZE]
the query grabs nothing... and i know records exist for the dates (this was when i was supplying w.wDate in the proper format)

i realise that the two dates i have supplied were string format, i was just playing around trying different ways to get it to work.

i would greatly appreicate an sql developer's input on this query, and also better ways to do it (i know im pretty inexperienced with in depth queries, and my way probably aint the most efficient)

cheers all
regards
adam
 
Format the date using .ToString("yyyy-MM-dd") when writing the data to the database.

This conforms to ISO 8601 which is the format you should always use when writing dates to a database.
 
are you talking about in the vb.net code or the sql code? i tried doing it on the vb.net end, but it wouldnt insert saying

Unable to cast object of type 'System.String' to type 'System.IFormatProvider'.


this is my code...

VB.NET:
[SIZE=2]cmd.Parameters.Item(0).Value = DateTimePicker1.Text.ToString([/SIZE][SIZE=2][COLOR=#800000]"yyyy-MM-dd"[/COLOR][/SIZE][SIZE=2])
[/SIZE]
i really hate working with dates in sql server, so much unnecessary agony.

if you could tell me where im going wrong that would be great.

regards
adam
 
Sorry I wasn't very clear - experiment with the following code and you should see the difference. Note: you should be using the Value of the control not the Text property.

VB.NET:
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MyDateNoTime, MyDateWithTime [/SIZE][SIZE=2][COLOR=#0000ff]As [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Date[/COLOR][/SIZE]
 
[SIZE=2]MyDateWithTime = DateTimePicker1.Value[/SIZE]
[SIZE=2]MyDateNoTime = [/SIZE][SIZE=2][COLOR=#0000ff]CDate[/COLOR][/SIZE][SIZE=2](DateTimePicker1.Value.ToString([/SIZE][SIZE=2][COLOR=#800000]"yyyy-MM-dd"[/COLOR][/SIZE][SIZE=2]))[/SIZE]
 
[SIZE=2]MsgBox(MyDateWithTime)[/SIZE]
[SIZE=2]MsgBox(MyDateNoTime)[/SIZE]

So you should try using:

VB.NET:
[LEFT][SIZE=2]cmd.Parameters.Item(0).Value = CDate(DateTimePicker1.Value.ToString([/SIZE][SIZE=2][COLOR=#800000]"yyyy-MM-dd"[/COLOR][/SIZE][SIZE=2]))[/SIZE][/LEFT]

This is the way I prefer to do it. There are other ways to achive the same result.
 
So going back to your original problem you should strip off the time before saving the data to the database.

If you have lots of date and time values stored already write a routine that loops through the table rows - loads the dates converts them using the above code and resaves them. I can help with this if you want - let me know.
 
hi,

your solution worked, but it still puts 12:00am after it... i suppose sql server puts it there as a default or something. this whole datetime crap is the one thing i really really hate about sql server. :mad:

thankyou for your help, it has been much appreciated

regards
adam
 
Try using T-SQL 'CONVERT' functions. 'CONVERT' function has three arguments. First argument specifies varchar(10) data type for returned value. Second argument is field/value to operated. Last argument defines 'MM/DD/YYYY' date formatting for returned value.

Your sql is re-written.

select'Item'=n.Description,
'Times Picked'=
(
select count(Qty)from tblWT where ID='000013388'
)
,
'Volume Picked'=
(
select sum(Qty)from tblWT WHERE ID ='000013388'
)

from tblWT w, tblID n WHERE n.ID ='000013388' and CONVERT(VARCHAR(10), w.wDate, 101) between '29/10/2006' and '2/11/2006' group by n.Description ORDER BY 'Times Picked'Asc
 
this is my query

VB.NET:
[SIZE=2]w[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]wDate [/SIZE][SIZE=2][COLOR=#808080]between[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'29/10/2006'[/COLOR][/SIZE][SIZE=2][COLOR=#808080]and[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'2/11/2006'[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]group[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]by[/COLOR][/SIZE][SIZE=2] n[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]Description [/SIZE][SIZE=2][COLOR=#0000ff]ORDER[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]BY[/COLOR][/SIZE][SIZE=2][COLOR=#ff0000]'Times Picked'[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Asc[/COLOR][/SIZE]

You will always, always, always get problems if you try to query a database date column using strings. In this case the database has to guess that your '29/10/2006' is a date in DD/MM/YYYY and convert the string to a date. If this same code is run against a DBMS with a different default date format (MM/DD/YYYY) then parsing will likely fail

If you need to submit dates, you should parameterise the query and set the type of the parameter to be a date
 
select'Item'=n.Description,
'Times Picked'=
(
select count(Qty)from tblWT where ID='000013388'
)
,
'Volume Picked'=
(
select sum(Qty)from tblWT WHERE ID ='000013388'
)

from tblWT w, tblID n WHERE n.ID ='000013388'and w.wDate between'29/10/2006'and'2/11/2006'groupby n.Description ORDERBY'Times Picked'Asc


Incidentally in standard ansi form this query would be:
VB.NET:
select
  n.Description AS item,
  count(*) as time_picked,
  sum(Qty) AS volume_picked
from
  tblWT w
  INNER JOIN
  tblID n
  ON
    n.ID = w.ID 
WHERE 
  n.ID ='000013388' and
  w.wDate between #29 oct 2006# and #2 nov 2006#
group by 
  n.Description 
ORDER BY times_picked

Observations:
Your query may scan the tables up to 3 times, unnecessarily when 1 scan will do. If SQLS doesnt realise that youre trying to make a correlated query (and the only correlation is using the string constant - it's not enough to let sqls presume that n.ID = w.ID)

Your query does a cartesian join of n and w.. If either contains a lot of rows, then you will be waiting a very, very long time for results that are meaningless (values will be too high)

If you require data from tables N and W, join them.. dont use in-select-list subqueries

There is the possibility that SQLS will translate every wDate to a string and assess that string to be between the strings '29/10/2006' and '2/11/2006'.. while i wont take the time out to work out what would happen in such a case you stand a good chance of getting spurious results if, e.g. '29/1/2006' as a string is determined to be between those strings - as a date it's not between but as a string it is (or maybe, for example).

Do try to avoid putting spaces in your identifiers - it makes things so much harder work on the client side because method names cant have spaces and you lose consistency. Either_use_underscores OrProperCaseDifferentiationForWords
 
VB.NET:
[SIZE=2][/SIZE]
[SIZE=2]MyDateNoTime = [/SIZE][SIZE=2][COLOR=#0000ff]CDate[/COLOR][/SIZE][SIZE=2](DateTimePicker1.Value.ToString([/SIZE][SIZE=2][COLOR=#800000]"yyyy-MM-dd"[/COLOR][/SIZE][SIZE=2]))[/SIZE]

This is the way I prefer to do it. There are other ways to achive the same result.

Why convert a date to a string and then back to a date with a legacy VB6 function?

Dim x As Date = DateTime.Now 'x has a time
Dim y as Date = x.Date 'just take the date part of x


Do note that it will always have a time component.. DateTimes ALWAYS have a time component, which is why they are called a DateTime. In .NET, Date and DateTime are synonymous

This is why we dont send strings to databases and hope they figure out that they are dates!!
 
ah ok, thanks cjard, thats actually pretty simple.

haha, it doesnt change the fact i hate the datetime data type :p

thanks to everyone though for all their input, it was greatly appreciated

have a good one guys
regards
adam
 
I love datetime.. And subbing them from each other to make a timespan! Cool!

The notion is: store a precise record of something, and then when showing to the user, that is when we dumb it down...

Format your datetimes to be date showing only
Use range selections in queries: a <= x < b
For all today, select where midnight_today <= record_date < midnight_tomorrow

Dont do: where remove_time(record_date) = remove_time(now)
 
what are the purposes of a timespan? i have a bit of trouble understanding what you just did.. sorry :eek: is that a timespan, or is that just more formatting stuff (i think tahts what it is...)

sorry mate
regards
adam
 
timespan is a span of time. when you subtract one date from another date, you get a number of hours,min,sec in between

i.e. today at 1500 hours, minus yesterday at 1400 hours is a timespan of 25 hours:

Imagine two datetimes:

Dim ts as Timespan = (dtHomeTimeToday - dtHomeTimeYesterday)

ts.Hours '24 hours!
ts.Minutes '0 minutes
ts.TotalMinutes '1440 minutes.. ie 24 hours, in terms of minutes


If you ever used DateDiff.. well, thats now how we do datediff.

-

The other stuff, i was trying to say when you do database operations, DONT do stuff that involves converting data types. If you have a million row table and you decide to say:

WHERE myDate = 'some string that looks like a date'

Then the db may either convert the string to a date, or convert 1 million dates to a string.
i.e. dont convert a million dates to being a string and do a string compare. Instead do a >= and < compare. ALl the dates today, are >= midnight today, and < midnight tomorrow. Dont do any operation on the milion dates, just compare them without converting them to anything
 
Back
Top