Anti-Rich
Well-known member
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
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
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]
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