ItCrazy.....Woow.w...w.
Group Blog
 
<<
สิงหาคม 2551
 
 12
3456789
10111213141516
17181920212223
24252627282930
31 
 
1 สิงหาคม 2551
 
All Blogs
 

ปัญหาเรื่อง DateTime ใน SQL Server

ปัญหาเรื่อง DateTime ใน SQL Server [20080801]



ตอนแรกก็จะเอาแค่ใน Team Project แต่ไหน ๆ ก็แล้ว จะทำทั้งทีก็เอาซะให้หมด
อันนี้เฉพาะ Project ที่เป็น DotNet นะครับ


ตามปกติในเรื่องของการใช้ข้อมูลที่เกี่ยวกับ Date, DateTime เรามักจะใช้ DateTimePicker หรือ MonthCalendar เป็น Control หลักในการให้ User ใช้กำหนดค่าข้อมูลใน field นั้น ๆ

ทีนี้ปัญหาแต่ดั้งแต่เดิมของเราก็คือเรื่องของ Format ที่ใช้ในการโยนค่าข้อมูล DateTime นี้กลับไปให้ SQL Server เก็บข้อมูลไว้

ซึ่งถ้าใช้เป็นแบบเดิมค่า ตามการจัด Format เช่น "dd/MM/yyyy" มันจะมีปัญหาเพราะ
มันจะจัดรูปแบบข้อมูลออกมาเป็น String ตาม Format ของเครื่องนั้นเช่น

MM-dd-yyyy

01-31-2008 [เครื่อง User ตั้งปีเป็น ค.ศ.]
หรือ 01-31-2551 [เครื่อง User ตั้งปีเป็น พ.ศ.]

จะเห็นว่าข้อมูลที่ได้ มันไม่เหมือนกัน พอเราจับ String ชุดนี้โยนเข้าไปใน SQL Server ค่าที่ได้จะผิดทันที
ถ้า !!!
เครื่อง User Set ปีเป็น พ.ศ.

เพราะอะไร
เพราะว่า Native ของการเก็บข้อมูลประเภท DataTime ใน SQLServer มันเป็น ค.ศ. เพราะฉะนั้น
ถ้าเราโยน 01-31-2551 เข้าไป SqlServer จะเข้าไปว่าเป็นปี ค.ศ. 2551 ทันที


รูปแบบการเก็บข้อมูลประเภท DateTime ของ SQLServer

yyyy-MM-ddT00:00:00
yyyy-MM-dd 00:00:00

ได้ทั้งสองรูปแบบ แต่แนะนำให้เป็นแบบแรกจะได้ไม่ต้องมีปัญหาเรื่อง Space ที่อยู่ระหว่าง dd และ 00

โยนรูปแบบเป็น String ได้เลย


วิธีแก้ แก้ยังไง
1. ถ้าใช้ DateTimePicker ในการป้อนข้อมูล
ในตัว DateTimePicker จะมี Property ที่ชื่อ value เป็นตัวเก็บค่าว่า เลือกวันเดือนปีอะไรอยู่ เวลาโยนข้อมูลให้ SQL Server สามารถโยนเป็น String ตามรูปแบบที่ SqlServer เก็บไว้ได้เลยเช่น

DocNo = DO-0001
StartDate = 1 สิงหา 2551
EndDate = 31 สิงหา 2551
Status = A

insert into tbTest(DocNo, StartDate, EndDate, Status) values('DO-0001', '2008-08-01T00:00:00', '2008-08-31T00:00:00', 'A')

[อย่าลืมว่า SQL Server เก็บเป็น ค.ศ.]
[ปล. DateTimePicker จะแสดงระบบตาม ปี ที่ User ตั้งไว้ในเครื่อง เปลี่ยนไม่ได้ เช่น เครื่องตั้ง พ.ศ. จะให้ DateTimePicker แสดง ค.ศ. ไม่ได้]

เวลาจะเอามา DateTimePicker มาใช้ก็จะเป็น

"insert into tbTest(DocNo, StartDate, EndDate, Status) values('DO-0001', " & Qt(DateTimePicker1.value.ToString("yyyy-MM-ddT00:00:00")) & ", " & Qt(DateTimePicker2.value.ToString("yyyy-MM-ddT00:00:00")) & ", 'A')"

อย่างนี้ ผิดครับ

เพราะลักษณะการอ้างถึงข้อมูล DateTime แล้ว ToString("....") จะเป็นการจัด Format ไม่ใช่ value ที่จะสามารถนำไปใช้กับทุกลักษณะของ ปี ที่ User Set ไว้ได้ เพราะ

ToString("yyyy-MM-ddT00:00:00")
ถ้าเครื่อง User ตั้งปีเป็น ค.ศ. จะเป็น 2008-08-01T00:00:00
ถ้าเครื่อง User ตั้งปีเป็น พ.ศ. จะเป็น 2551-08-01T00:00:00

มันก็ไม่เหมือนกันอีกหน่ะแหล่ะ

วิธีการแก้แบบเดิม ๆ

จะใช้เป็น "'" & DateTimePicker1.value.Year.ToString() & DateTimePicker1.value.ToString("-MM-ddT00:00:00") & "'"

วิธีนี้เป็นการนำเอาตัวแปร DateTime ที่ DateTimePicker.Value เก็บไว้ โดยอาศัย Property year ที่จะคืนค่าปี ค.ศ. [เท่านั้น ไม่ว่าเครื่องจะเป็น พ.ศ. หรือเป็นอะไร] มาให้

แล้วค่อยเอาไปต่อกับชุดหลังคือ DateTimePicker1.value.ToString("-MM-ddT00:00:00") จะได้เป็น 2008-08-01T00:00:00 ออกมาในรูปแบบที่ ถูกต้อง

ที่นี้ มันอาจจะค่อนข้างยุ่งยากเพราะ ต้องตัดเอาตรงโน้น มาต่อตรงนี้ หลายขั้นหลายตอน มันเหนื่อย แล้วก็ดู Code ยุ่ง ๆ ก็เลยมีการเขียนเป็น Function ไว้ใช้งานเพื่องานนี้โดยเฉพาะ แต่ทีนี้ เราจะไม่ว่ากันถึงเรื่อง Function ที่เขียนไว้ช่วยจัดการ

เพราะ วันนี้ เรามีทางเลือกใหม่ที่ สะดวกสบายกว่าเดิม ไม่ง้อ Function กลาง Team Leader จะไม่เขียนให้ เราก็ไม่ง้อ โดย

DateTimePicker1.value.toString("s")

แค่นี้เลยครับ

ไม่ต้องไปต่อ String ใด ๆ ทั้งสิ้น ไม่ต้องกังวลเรื่องเครื่อง User จะเป็น พ.ศ. หรือ ค.ศ. เพราะสิ่งที่มันคืนค่ารูปแบบออกมาให้ มันเป้นรูปแบบที่เตรียมเพื่อ SqlServer โดยเฉพาะ [ไม่รู้ว่า Database Server อื่นเป็นรึป่าว ยังไม่ได้ลอง]

เช่น เลือก DateTimePicker1 เป็น วันที่ 15 เดือนธันวา ปี พ.ศ. 2552 มันก็จะได้
2009-12-01T00:00:00
เอาไปโยนใส่ Insert Query ได้เลยครับ


ปัญหาที่ 2 ที่ตามมาก็คือ
DateTimePicker ดันทะลึ่งเก็บเวลาได้ด้วย ซึ่งตาม Requirement บอก Spec บอกไว้ว่า ชั้นจะให้เธอเก็บเฉพาะวัน ชั๊นไม่เอาเวลา เพราะต่าง ๆ นา ๆ เช่น เวลา Query ดูมันไม่สวยงาม

ไม่มีอะไรที่ Programmer ทำไม่ได้ มีแต่ไม่ได้ทำ [เท่านั้นแหล่ะ]

ถ้าเป็นการจัดข้อมูลเองไม่ใช้ DateTimePicker มาช่วย ก็อาจจะไม่มีปัญหาในส่วนนี้ แต่ในที่นี้เราใช้

Trick สั้น ๆ ง่าย ๆ คือ

ตามปกติเวลาเราจะทำการ Set ให้วันใน DateTimePicker เป็นปัจจุบัน เราจะ Set เป็น

DateTimePicker1.value = Now

ปัญหาคือ Now มันหมายถึง วันเวลา ณ ปัจจุบัน มันก็เลยทำให้ DateTimePicker เป็นปัจจุบันจริง ๆ คือติดเวลามาด้วย เราต้องใช้เป็น

DateTimePicker1.value = Today

หรือแบบเต็ม ๆ ก็คือ

DateTimePicker1.Value = DateTime.Today

ได้คืบจะเอาศอก เราบอกให้
ถ้าจะเอาเป็นว่า เอาล่วงหน้าไปอีก 3 วัน ก็จะเป็น
DateTimePicker1.Value = DateTime.Today.AddDays(3)
หรือถอยหลังไป 3 วัน
DateTimePicker1.Value = DateTime.Today.AddDays(-3)

จะเดินหน้า ถอยหลัง ก็ดู Property ข้างเคียงกันนะครับ
ที่นี้ พอเอาไปใช้
DateTimePicker1.Value = DateTime.Today
DateTimePicker1.Value.ToString("s")
ก็จะได้เป็น
2008-08-01T00:00:00
ใช้การได้เรียบร้อย
สรุป.

1. Format ในการเก็บข้อมูลแบบ จริง ๆ จัง ของ DateTime ใน SqlServer คือ 'yyyy-MM-ddT00:00:00' [yyyy = ค.ศ.]
2. Now = วันปัจจุบัน (เครื่อง User) ที่รวมเวลาด้วย
3. Today= วันปัจจุบัน (เครื่อง User) แต่ไม่เอาเวลา [00:00:00]
4. เวลาใช้งานใช้เป็น

DateTimePicker.Value <= ตัวแปรประเภท DateTime
Dim d as Date = Now <= ตัวแปรประเภท DateTime
หรือ Dim d as Date = DateTimePicker.Value <= นี้ก็ตัวแปรประเภท DateTime

d.ToString("s") ' จบเลย

อ่อเวลาเอาโยนให้ SQL Server อย่าลืมใส่ Single Quote ด้วย เป็น

"'" & d.ToString("s") & "'"

5. เวลาใช้การโยนค่าเป็นแบบ Parameter ใน SqlCommand Object ก็ใช้ได้เหมือนกัน เช่น cmd.Parameter.AddWithValue(“@date”, d.ToString("s")) หรือจะให้ลักษณะให้ตัวแหรมันคุยกันเองก็ได้ cmd.Parameter.AddWithValue(“@date”, d)

6. เวลา Click ขวาที่ Table แล้วเลือก Open Table ใน SQL Server / EnterpriceManager มาจะแสดงข้อมูลใน field DateTime เป็นไปตาม Format หรือระบบปีของเครื่อง ๆ นั้นนะครับ ถ้าอยากรู้ว่า DateTime ที่เป็นจริง ๆ ในเป็นยังไงต้อง Query เอา (ทั้ง SQLServer 2000 และ 2005)

ปล.
1. QT() ที่พบเห็นในบางจุด ประมาณว่าลืมแก้ มันเป็น Function ที่เขียนขึ้นเพื่อเติม Single Quote ให้กับ String นะครับ
2. ถ้าเห็นว่ามันยุ่งยาก ก็ปล่อยผ่านมันไป หรือมีวิธีที่ดีกว่า ก็ไม่ขัดข้องที่จะน้อมรับนะครับ





 

Create Date : 01 สิงหาคม 2551
0 comments
Last Update : 1 สิงหาคม 2551 12:21:31 น.
Counter : 3446 Pageviews.

ชื่อ : * blog นี้ comment ได้เฉพาะสมาชิก
Comment :
  *ส่วน comment ไม่สามารถใช้ javascript และ style sheet
 
รหัสส่งข้อความ
กรุณายืนยันรหัสส่งข้อความ


itCrazy
Location :


[ดู Profile ทั้งหมด]

ให้ทิปเจ้าของ Blog [?]
ฝากข้อความหลังไมค์
Rss Feed

ผู้ติดตามบล็อก : 1 คน [?]




Friends' blogs
[Add itCrazy's blog to your web]
Links
 

 Pantip.com | PantipMarket.com | Pantown.com | © 2004 BlogGang.com allrights reserved.