-1

In my Android App, I want to insert date from DatePicker to SQL Server DB. The Date type in SQL Server is datetime. I can get Day and Month and Year from DatePicker like this :

                int day = datePicker.getDayOfMonth();
            int month = datePicker.getMonth();
            int year = datePicker.getYear();

But how should i pass it to SQL Server 2012? With which format?

Alireza
  • 89
  • 2
  • 15

3 Answers3

1

It depends on which database you want to store. If you are storing locally in your sqlite database I will recommend store the date in millis and if you are storing in backend server you can convert it to ISO date format. Examples of both way is given below, and if you want date in any other format you can do that too by modifying string passed to constructor of STANDARD_UI_DATE_FORMAT object.

Calender startTimeCalender = Calendar.getInstance();    
startTimeCalender.set(Calendar.YEAR, year);
startTimeCalender.set(Calendar.MONTH, monthOfYear);
startTimeCalender.set(Calendar.DAY_OF_MONTH, dayOfMonth);

//Date in Millis
lond dateInMillis = startTimeCalender.getTimeInMillis();

SimpleDateFormat STANDARD_UI_DATE_FORMAT = new SimpleDateFormat("yyyy-MM-ddTHH\:mm\:ss.fffffffzzz");

//Date in ISO Format
String formattedDate = STANDARD_UI_DATE_FORMAT.format(startTimeCalender.getTime())
sschuberth
  • 28,386
  • 6
  • 101
  • 146
himanshu1496
  • 1,921
  • 19
  • 34
  • It's not a local DB, its in another Server. And i need just date,so its better to change the Type of field in DB to just date, and not time. And you think its better to use Calender? Or i have to? – Alireza Nov 24 '15 at 11:31
  • If you are playing with date or time or both, Calender object is the best thing to do. And I am not really sure about what date format your server takes but if need to send it as a String you can use above code for formatting. In my app we are using ISO format, so I convert to ISO and send it as a string. – himanshu1496 Nov 24 '15 at 12:04
  • The SimpleDateFormat you use did not work for me, I used this : SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss.mmm"); But when i compile i got this error in catLog : java.lang.NumberFormatException: Invalid int: "18 12:00:00.000" That 18 is the dayOfMonth and the next is time. – Alireza Nov 24 '15 at 16:16
  • Can you show me the code you used to format the date, because I want to understand how is this coming with calendar object? – himanshu1496 Nov 25 '15 at 07:24
  • Calendar finalCalendar = Calendar.getInstance(); int fDay = datePicker2.getDayOfMonth(); int fMonth = datePicker2.getMonth(); int fYear = datePicker2.getYear(); finalCalendar.set(Calendar.YEAR, Integer.valueOf(fYear)); finalCalendar.set(Calendar.MONTH, Integer.valueOf(fMonth)); finalCalendar.set(Calendar.DAY_OF_MONTH, Integer.valueOf(fDay)); finalCalendar.set(Calendar.HOUR, 0); finalCalendar.set(Calendar.MINUTE, 0); finalCalendar.set(Calendar.SECOND, 0); – Alireza Nov 25 '15 at 08:28
  • SimpleDateFormat fSimpleDateFormat = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss.mmm"); setFinalDate(fSimpleDateFormat.format(finalCalendar.getTime())); FinalDate is the String . And i convert it to Date by Using this : java.sql.Date.valueOf(getStartDate()); – Alireza Nov 25 '15 at 08:28
  • Here finalDate and startDate are same variable? – himanshu1496 Nov 25 '15 at 08:42
  • No, i made a mistake in comment. We have a startDate and finalDate. I Edit this as : java.sql.Date.valueOf(getFinalDate()); The code above are use the same for startDate. – Alireza Nov 25 '15 at 08:46
  • I think i found the problem, now i should solve this. I create a button and show the return date from the code above. The Month is zero. What Month i select, it returns zero. For example i select Nov 2015 24, it shows me : 2015-00-24 12:00:00 – Alireza Nov 25 '15 at 09:13
  • then check if your picker is returning the correct value of month. And if my answer helped you resolve your problem please mark it correct. – himanshu1496 Nov 25 '15 at 10:23
  • No, it still have problem. I did this and it returns the right value now : String fMonth = String.valueOf(datePicker2.getMonth()); And : finalCalendar.set(Calendar.MONTH, (Integer.parseInt(fMonth))); now it returns right month int. But still in Locat : java.lang.NumberFormatException: Invalid int – Alireza Nov 25 '15 at 10:31
  • It seems like Integer.parseInt() is returning this exception, and if that's the case then fMonth is does not have a numerical value. And no need it parse it two times just do this: int fMonth = datePicker2.getMonth(); finalCalendar.set(Calendar.MONTH, fMonth); – himanshu1496 Nov 25 '15 at 10:45
  • No, it still have problem. with this solution still problem. I changed the MM to mm and no luck. – Alireza Nov 25 '15 at 12:07
  • change that mm to MM, mm is for minute. Make it as below and tell me if it is working or not. SimpleDateFormat fSimpleDateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.mmm"); – himanshu1496 Nov 25 '15 at 12:12
  • :-< NO. it exactly show me in Logcat: java.lang.NumberFormatException: Invalid int: "19 12:00:00.000" Wich 19 is the day and 12:00:00:000 is hour,minute,second,mili second. But when i insert this format in DB and execute.it shows me correct fields. – Alireza Nov 25 '15 at 12:22
  • Dude, for SimpleDateFormat, mm denotes minutes, you can check the developer website [link](http://developer.android.com/reference/java/text/SimpleDateFormat.html) – himanshu1496 Nov 25 '15 at 12:42
  • No chance dude. It exactly shows the right format when i use : Toast.makeText(getApplicationContext(), getFinalDate(), Toast.LENGTH_SHORT).show(); It shows my datetime correctly. And when i pass it to Store Procedure of SQL Server DB, it gives me : java.lang.NumberFormatException: Invalid int – Alireza Nov 25 '15 at 15:43
  • Then you have to check that what format SQL server DB is expecting. – himanshu1496 Nov 26 '15 at 07:14
0

You can convert a string (as in the example provided by himanshu1496) to a valid datetime type on SQL Server using the CONVERT() function.

So as long as you can get the string value to the database you could do something like this:

SELECT 
   '2015-11-24 14:17:19' AS UnconvertedText,
   CAST('2015-11-24T14:17:19' AS datetime) AS UsingCast,
   CONVERT(datetime, '2015-11-24T14:17:19', 126) AS UsingConvertFrom_ISO8601 ;
GO

(Dont forget that sneaky T in the string in the example above)

It is more detailed described in the documentation here: https://msdn.microsoft.com/en-us/library/ms187928.aspx

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
WernerW
  • 792
  • 11
  • 27
-1

Save the date as miles into your SQL database.

sschuberth
  • 28,386
  • 6
  • 101
  • 146
Josh.M
  • 103
  • 4