Date in string format has to be changed datetime format

I have date coming to one page as a string in the following format
"May 4 2005 12:00AM"

I need to query one of my tables using this date in combination of other nondate values. How can I convert this date into valid sql server datetime format before I query a database tables

Please help

Hi,
you can use Sql function CONVERT :
convert(datetime,MYPARAM,110)
|||That format will work -- depending on your locale. Therecommended date format for SQL Server is the ISO format: YYYYMMDDHH:MM:SS.MS
Note that :SS.MS can be left off, or the entire time portion can be left off and the time will default to 12:00 a.m.
Is that what you're looking for?
|||

Hi there, I have some problem here, please help.

I use Format funtion as the format is "dd-MMM-yy" and my input date is "18/09/2005". system return me 4 char month instead of 3 char. 18-Sept-05. Anyone has idea how to solve this?

The regional setting of the PC is ok. So what other setting have been left off? IM using Visual Studio .net 2003 and working on XP professional with SP1 pc. Thanks!

date function in C#

hi experts,

i'm working in a web page for some statistics and i have a calendar where the customer can choose a day a week or a month and according to the date he select i need to query the database according to the date selected.

what i want to know is how can i store the date for a day in a variable so i can call it from a stored procedure, the day actually is easy what i want is how can i store the whole week in a variable so i can give it to the stored procedure and query the data in the database according to the whole week may be with startday and endday

also the same problem for the whole month, any idea how can i implement that in C#?!!!

thanks

Are you asking how would you query by a date range? such as a week?

-c

|||

thanks for your reply, what i wnat is how can i store the value of the whole week and month in a variable. i know how to query the database. the situation is as follow:

i have a web site where a client can select a day week or a month to see some statistics, for the day i know how to do it. and i have also stored procedures. what i want is how to store the startday and end day in a variable when the client select a week or a month, so i can assign these variables to the stored procedure so the stored procedure can query the database and return the data for just the specific selected date.

i hope i made my self clear.

any help will be appreciated.

thanks

|||

mrjoka

Check out the article below and look for the sample function called RunStoredProcParams here you will see how to return a datareader from a stored procedure that accepts a parameter

http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx

HTH

|||

thanks for the link, actually what i want is how to store the date value of the selected week or month from a callendar in a variable so i can assing them to a stored procedure. i know how to assign parameters to SP but how can i store the startdate and enddate selected from the user from the callendar? for example if the user select a week or a month for a day i can use :date1 = Calendar1.SelectedDate.ToShortDateString(); but for a week or a month!!!!

this is what makes me crazy these days

thanks in advance

|||

Since the SelectedDate is a DateTime variable you can do this for month;

int SelectedMonth = Calendar1.SelectedDate.Month;

The week can be done as follows

System.Globalization.Calendar cal = System.Globalization.CultureInfo.CurrentCulture;int SelectedWeek = cal.GetWeekOfYear;

|||

thanks for the reply it realy helps but when i tried the week i had these fouts:

Error 1 Cannot implicitly convert type 'System.Globalization.CultureInfo' to 'System.Globalization.Calendar'

Error 2 Cannot convert method group 'GetWeekOfYear' to non-delegate type 'int'. Did you intend to invoke the method?

any idea why?

thanks again

|||

yes, I forgot the .Calendar on the CultureInfo - so its = System.Globalization.CultureInfo.CurrentCulture.Calendar;

System.Globalization.Calendar cal = System.Globalization.CultureInfo.CurrentCulture.Calendar;

That will fix both issues.

-c

|||

thanks for the quick reply,

i did wat you told me and i get rid of the first fout but the second one is still there

any idea

thanks

|||

my bad - you need to add () to the call;

int SelectedWeek = cal.GetWeekOfYear();

|||

sorry to bother you again,

here is the fout this time:

Error 1 No overload for method 'GetWeekOfYear' takes '0' arguments

is it because i'm using it inside a case statement?!!!

thanks

|||

here you go;

int SelectedWeek = System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(Calendar1.SelectedDate, System.Globalization.CalendarWeekRule.FirstFullWeek, DayOfWeek.Monday);

-c|||

do i need to declareDayOfWeek because it gave me this error:

Error 1 The name 'DayOfWeek' does not exist in the current context

thanks

|||

thanks man it works like a charm

|||

DayOfWeek is under System and is an enumeration so it should be fine. Maybe try System.DayOfWeek.

-c

Date format problem with SQL server

Hi.
I'm localized in Greece and the date format used is dd/mm/yyyy.

So I have is that I made a page with a callendar. The user picks a date and this date is stored to an SQL server. But here comes the problem. When I try to write to the server the above format ( dd/mm/yyyy ) is not accepted because SQL wants date in format ( mm/dd/yyyy). So if day is bigger than 12 I get error or if is less than 13 wrong date is stored in the SQL.

Any ideas?
Thanks in advanceWhen you are in code, be it SQL or C#/VB, always use the date data type NOT a string. When you display the contents of a date data type always use the appropriate culture. When you pass a value to SQL use an ado paramater of datetime. There should be no problem then. If, and I can't think why, you really want to pass a string to SQL with a date in it, then use a culture invariant such as YYYY-MM-DD|||Thanks for your quick reply.

No I'm not passing string to the SQL. Passing date data type. I read it as date, I use it as date in the calendar, and I try to write it as date in the SQL. but fail. :(|||That doesn't make any sense. Can you post how you are using it in SQL server?|||I read it as follow:
Dim cmd As New OleDbCommand("SELECT * from NEWS where id = " & Convert.ToInt64(myGlobalId), con1)

And I get no problem there. It reads correctly and displays correctly. all the fields.

And I write to the db as follow:

Dim cmd As New OleDbCommand("INSERT INTO NEWS (id, title, body, news_date) VALUES (" & newid & ",'" & titlebox.Text & "','" & bodybox.Text & "','" & mySdate & "')", con1)

mySdate has been declared as date and as datetime but no use. Also it has been test with and without quotes ('). Without quotes I get a new record but date is always 1/1/1900. With quotes I get an error when day is bigger than the month.

Thanks for your time...|||You are using a string! You must try to use params, for many many reasons, one of them is sorting out formating problems like you've got. Switch to using params and the problem will go away.
PS You really should worry about SQL Injection hacks so using params will get you around 99% of those too.|||What do you mean about security? Do you have a link so I read more about SQL Injection hacks?

I guess I'm sending a string as long as I use ['mySdate'] but I do not as long I send [mySdate]. When I send just [mySdate] I get a 1/1/1900 date in the SQL db. So the error should be elsewere. Also I found is that when a ODBC/System DNS is created there is an option "Use regional settings when outputing currency, numbers, dates and times" that can be checked/unchecked. But can't find somthing similar when creating my connection in visual studio...

Once more thanks for helping a newbie :)|||There are plenty of resources on SQL Injection, just type it into google and choose the one at the level most suited. Basically imagine someone has entered "'Delete from Stock" as a text string, it could actually run the SQL typed in, not what you want at all!

I didn't really follow what your statement, but trust me use paramaters. Look up SQLParameter and Commands. Your SQL should look more like...

"select * from table where column=?"

The question mark is a place holder for your first param. Look it up, honestly its much easier than I'm making it. But you really need to spend 20 mins and have a good read.|||You were right.
I used parameters and date problem solved. I have put the ? as you suggested and after I have the command:

"cmd.Parameters.Add("@.news_date", OleDbType.Date).Value = mydate"

Also seems that there is a protection for SQL injections... :)
The same for the other fields of course. :) Seems to work great now!

Thanks for your help.

Date format passing parameter to WebService

Hi Lads,
I'm having this wee problem with the date format of a parameter - I'm
building a list of the needed parameters on a web page (determined from the
report) via the API, I have set the report to English(Ireland), I assumed
this would take care of the date format (dd/MM/yyyy) rightly or wrongly I'm
not too sure - reigonal settings on my PC are the same. The problem that I
have is that when I pass back the date 30/09/2005 it comes back with an error
- any suggestions? they would be most appreciated.You can use strings instead DateTime, and you will avoid any TimeZone
convertion and reginal settings.
Jerry
http://www.rdlcomponents.com
"Rowan Massey" wrote:
> Hi Lads,
> I'm having this wee problem with the date format of a parameter - I'm
> building a list of the needed parameters on a web page (determined from the
> report) via the API, I have set the report to English(Ireland), I assumed
> this would take care of the date format (dd/MM/yyyy) rightly or wrongly I'm
> not too sure - reigonal settings on my PC are the same. The problem that I
> have is that when I pass back the date 30/09/2005 it comes back with an error
> - any suggestions? they would be most appreciated.|||Hi Jerry,
Unfortunately I can't use strings! I actually build up parameters on the web
page depending on their type in the report itself! i.e. for datetime
parameters, I create a text box an image control next to it to popup a date
selection screen, for a string I create a textbox only etc. I'm trying to
make it as generic as possible so I only have to do this once!
"Jerry" wrote:
> You can use strings instead DateTime, and you will avoid any TimeZone
> convertion and reginal settings.
> Jerry
> http://www.rdlcomponents.com
> "Rowan Massey" wrote:
> > Hi Lads,
> >
> > I'm having this wee problem with the date format of a parameter - I'm
> > building a list of the needed parameters on a web page (determined from the
> > report) via the API, I have set the report to English(Ireland), I assumed
> > this would take care of the date format (dd/MM/yyyy) rightly or wrongly I'm
> > not too sure - reigonal settings on my PC are the same. The problem that I
> > have is that when I pass back the date 30/09/2005 it comes back with an error
> > - any suggestions? they would be most appreciated.

Date comparison problem

Hi

I have a table that contains information that has start dates and end
dates. They are stored in short date format.

I have built a web page that initially returned all the information. I
then want to return information spcific to todays date, where I used

dim todDate
todDate = now

then I did

shTodDate = FormatDateTime(todDate, 2)

which returned the date to a web page that was in the same format as
what was in the access table.

SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"

This was fine beacuse it returned all events that started on that day.
This doesn't help though because some events last a few days, so I
tried the following

SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
endDate<=#'&shTodDate&'#;

This still only returned events that started on the current day.

Does anyone know how I can use <= or >= when comparing dates?

Thanks
puREp3s+puREp3s+ (colin42@.btinternet.com) writes:
> SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
> endDate<=#'&shTodDate&'#;
> This still only returned events that started on the current day.
> Does anyone know how I can use <= or >= when comparing dates?

Maybe the folks over in comp.datatabases.ms-access knows? It seems
that you are using Access, from the syntax. At least it is not MS
SQL Server.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It looks like you have your comparison operators backwards. In TSQL syntax,
this is what you would do to get any events in progress at this moment:

DECLARE @.t DATETIME
SET @.t = CURRENT_TIMESTAMP

SELECT *
FROM Events
WHERE stDate <= @.t AND endDate >= @.t;

... or, if the end date may be NULL, then:

SELECT *
FROM Events
WHERE stDate <= @.t AND (endDate >= @.t OR endDate IS NULL);

Hope that helps,
Rich

"puREp3s+" <colin42@.btinternet.com> wrote in message
news:7a6f18a3.0310211330.7ad2a1d0@.posting.google.c om...
> Hi
> I have a table that contains information that has start dates and end
> dates. They are stored in short date format.
> I have built a web page that initially returned all the information. I
> then want to return information spcific to todays date, where I used
> dim todDate
> todDate = now
> then I did
> shTodDate = FormatDateTime(todDate, 2)
> which returned the date to a web page that was in the same format as
> what was in the access table.
> SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"
> This was fine beacuse it returned all events that started on that day.
> This doesn't help though because some events last a few days, so I
> tried the following
> SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
> endDate<=#'&shTodDate&'#;
> This still only returned events that started on the current day.
> Does anyone know how I can use <= or >= when comparing dates?
> Thanks
> puREp3s+

Date Compare in SQL Server Stored Procedure

Hi,

i have "req_date" column of "datetime" type in Database table besides
other columns.

From my Web page, i am calling the Stored Procedure with variable
parameter "Search_Date" of Varchar(60) type.

the value, i am passing to Stored procedure through "Search_Date" is
compared to req_date column of table.

My question is that how to do this comparision of date in WHERE part of
Select statement within Stored Procedure?

ThanksWhy are you using a varchar parameter when the correct data type is
datetime? I would fix the front end to use the proper data type, rather
than hack in something on the server side - it will also help to
prevent invalid data and SQL injection attacks.

If you really want to do it on the server, you can CAST() or CONVERT()
to datetime, but that's a workaround, not a solution.

Simon|||Use a DATETIME parameter rather than a VARCHAR otherwise you'll just
have to convert the value. Don't rely on implict conversion for date
strings because it is subject to regional settings on the server and
the connection.

Assuming @.search_date is a DATETIME:

WHERE req_date = @.search_date

or, if you just what to search on the date and ignore the time:

WHERE req_date >= @.search_date AND req_date <
DATEADD(DAY,1,@.search_date)

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Use a DATETIME parameter rather than a VARCHAR otherwise you'll just
> have to convert the value. Don't rely on implict conversion for date
> strings because it is subject to regional settings on the server and
> the connection.

It's worth pointing out here that the regional settings that David
are talking about are *not* those of Windows. Instead SQL Server
has its own settings that can affect how date literals are interpreted.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp