SQLShack
Introduction
Yhteinen tehtävä aloittelijoille on oppia tekemään SQL muuntaa päivämäärän ja työtä muuntaa ne päivämäärän muihin tietotyyppeihin tai muuntaa muita tietotyyppejä päivämääräksi.
Täällä tässä artikkelissa selitämme, miten työskennellä ja muuntaa päivämääriä eri muotoihin tai päinvastoin.
Tarve
- SQL Server asennettu. Alkaen SQL Server 2008
Esimerkki
Ensimmäinen esimerkki on yksinkertainen, meillä on taulukossa varchar-sarake, jossa on päivämäärä, mutta meidän on muunnettava varchar päivämääräksi. Meidän on tehtävä SQL convert date.
Tässä on skripti, jolla luodaan taulukko datalla:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
CREATE TABLE .(
NOT NULL,
(100) NULL,
CONSTRAINT PRIMARY KEY CLUSTERED
(
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
) ON
GO
INSERT . (, ) VALUES (1, N’02-03-2005′)
INSERT . (, ) VALUES (2, N’03-05-2006′)
INSERT . (, ) VALUES (3, N’04-05-2011′)
|
Haluamme muuntaa sarakkeen date sarakkeesta nvarchar(100) päivämääräksi.
Sitä varten yritämme muuttaa taulukon suunnittelua:
Yritämme muuttaa tietotyypin smalldatetime:
Saat seuraavan virheilmoituksen:
Muutosten tallentaminen ei ole sallittua. Tekemäsi muutokset edellyttävät, että seuraavat taulukot poistetaan ja luodaan uudelleen. Olet joko tehnyt muutoksia taulukkoon, jota ei voi luoda uudelleen, tai ottanut käyttöön vaihtoehdon Estä sellaisten muutosten tallentaminen, jotka edellyttävät taulukon luomista uudelleen.
Voit ratkaista tämän virheen siirtymällä SSMS:ssä Työkalut > Asetukset-valikkoon:
Siirry Asetukset-valikossa kohtaan Suunnittelijat >Taulukko- ja tietokantasuunnittelijat ja poista valinta Estää sellaiset muutosten tallentaminen, jotka edellyttävät taulukon luomista uudelleen:
Tämä vaihtoehto poistaa käytöstä mahdollisuuden estää taulukon uudelleenluomisen tallentaminen. Kun muutat sarakkeen tietotyyppiä, se edellyttää taulukon uudelleenluomista.
Nyt voit tallentaa suunnittelun ja taulukkosi muunnetaan päivämääräksi ja SQL muuntaa päivämäärän valmiiksi:
Muunnosfunktiot
T-SQL:ssä on funktioita tietotyyppien muuntamiseen. Käytämme CAST- ja CONVERT-funktioita SQL-muunnospäivän tekemiseen.
Aloitetaan ensin CAST:
Miten muunnetaan varchar, nvarchar, char, nchar sql-päivämääräksi käyttäen CAST:ia
Seuraavassa esimerkissä, näytetään miten muunnetaan merkit datetime-päivämäärätyypiksi käyttäen CAST-funktiota:
|
1
2
|
declare @vardate varchar(100)=’03-04-2016′
select CAST(@vardate AS datetime) AS dataconverted;
|
Esimerkissä julistetaan muuttuja nimeltä vardate, ja sitten tämä muuttuja, joka on varchar, muunnetaan datetime-arvoksi CAST-funktiolla.
Huomautus: Lisätietoja CAST-funktiosta on tässä linkissä: CAST ja CONVERT (Transact-SQL)
Miten SQL muuntaa päivämäärän varcharista, nvarcharista, charista, ncharista päivämääräksi käyttämällä CONVERTia
CONVERT on funktio, joka voi tehdä saman kuin CAST edellisessä skenaariossa.
|
1
2
|
declare @vardate varchar(100)=’03-04-2016′
select CONVERT(datetime, @vardate) as dataconverted
|
T-SQL-koodi tekee saman kuin cast, mutta se käyttää CONVERT-funktiota. CONVERT-funktion etuna on, että päivämäärän muotoa voi helposti muuttaa style-argumentin avulla.
Jos esimerkiksi haluat päivämäärän ISO-muodossa, voit käyttää seuraavaa T-SQL-lauseen:
|
1
|
select CONVERT(nvarchar(30),getdate(), 121) as isoformat
|
Miten muunnetaan sql-päivämäärä eri muotoihin T-SQL:ssä
Seuraavassa esimerkissä näytetään, miten päivämäärän muoto muunnetaan eri muotoihin.
Japanilaiselle formaatille:
|
1
|
select CONVERT(nvarchar(30),getdate(), 111) as Japaniformaatille
|
USA:n formaatille:
|
1
|
select CONVERT(nvarchar(30),getdate(), 110) as USAformat
|
Iso-Britannian formaattia varten:
|
1
|
select CONVERT(nvarchar(30),getdate(), 103) as Brittiformaatti
|
Saksalaiselle formaatille:
|
1
|
select CONVERT(nvarchar(30),getdate(), 104) as Germanformat
|
Italiankieliselle muodolle:
|
1
|
select CONVERT(nvarchar(30),getdate(), 105) as Italianformat
|
Eurooppalaiselle oletusformaatille:
|
1
|
select CONVERT(nvarchar(30),getdate(), 113) as EuropeDefaultformat
|
ODBC:n kanoniselle:
|
1
|
select CONVERT(nvarchar(30),getdate(), 120) as ODBCCanonicalformat
|
Sinulla on aina mahdollisuus käyttää FORMAT-funktiota saadaksesi päivämäärän haluamassasi muodossa:
|
1
|
SELECT FORMAT( getdate(), ’dd/MM/yy’)
|
FORMAT-funktio helpottaa päivämäärien käsittelemistä ja haluamasi formaatin käyttämistä, koska sinun ei tarvitse tuntea tyylisuuntaa. Kokemukseni mukaan näen kuitenkin paljon koodia, jossa käytetään CAST- ja CONVERT-funktioita, joten on parempi tuntea ne.
Huomautus: Lisätietoja FORMAT-funktiosta saat tästä linkistä: FORMAT (Transact-SQL)
Problems related to SQL convert date operations
Kun yrität muuntaa päivämääräksi, se ei aina ole mahdollista. Seuraavassa esimerkissä näkyy yleinen virhe:
|
1
2
|
declare @vardate varchar(100)=’11242016′
select CONVERT(datetime, @vardate) as dataconverted
|
Virheilmoitus on seuraava:
Msg 242, Level 16, State 3, Line 22
Varchar-tietotyypin muuntaminen datetime-tietotyypiksi johti alueen ulkopuoliseen arvoon.
Tarvitset päivämäärän erottimia, kuten ”/”, ”.” tai ”-”.
Seuraavassa esimerkissä merkkijono muutetaan 11242016:sta 11-24-2016:ksi ja muunnetaan sitten sql-päivämääräksi:
|
1
2
3
4
5
|
declare @vardate varchar(100)=’11242016′
set @vardate= SUBSTRING(@vardate, 1, 2)+’-’+ SUBSTRING(@vardate, 3, 2)+’-’+SUBSTRING(@vardate, 5, 4)
select CONVERT(date, @vardate) as dataconverted
|
Käytämme substring-merkkijonoa ”-”:n ketjuttamiseen hyväksyttävän päivämäärämuodon käyttämiseksi, minkä jälkeen muunnamme merkit merkkien muuntamiseksi sql-päivämääräksi CONVERT-funktiolla.
Päivämäärän tietotyypit
SQL Serverissä on useita päivämäärän tietotyyppejä:
- Time palauttaa tunnit, minuutit, sekunnit ja nanosekunnit (hh:mm:ss.nnnnnnnn)
- Date palauttaa vuoden, kuukaudet ja päivät (vvvvv-mm-kk)
- Datetime palauttaa datan tässä muodossa: YYYY-MM-DD hh:mm:ss
- Smalldatetime palauttaa päivämäärän tässä muodossa: YYYY-MM-DD hh:mm:ss
- Datetime2 on samanlainen kuin Datetime, mutta siinä on enemmän tarkkuutta (YYYY-MM-DD hh:mm:ss)
- Datetimeoffset sillä on datetime2:n tarkkuus, mutta sitä käytetään aikavyöhykkeisiin UTC:ssä
SQL muuntaa päivämäärän kokonaisluvuksi
Jos käytät CONVERT- tai CAST-toimintoa konvertoidaksesi datetime-ajan kokonaisluvuksi, se palauttaa päivien lukumäärän vuodesta 1900 lähtien annettuun päivämäärään asti.
Esimerkiksi seuraava T-SQL-koodi näyttää päivien määrän vuodesta 1900 tähän päivään asti:
|
1
|
SELECT CONVERT(INT, GETDATE())
|
Voit myös muuntaa datetime-arvon vuoden, kuukauden, päivän jne. kokonaisluvuksi. Seuraavassa koodissa näytetään, miten datetime-arvon päivä, kuukausi ja vuosi tallennetaan kokonaislukumuuttujiin:
|
1
2
3
4
4
4
4
4
5
|
declare @vuosi int=vuosi(getdate())
declare @month int = month(getdate())
declare @day int = day(getdate())
select @year as year,@month as month,@day as day
|
Yleisiä kysymyksiä SQL:n päivämäärän muuntamisesta SQL Serverissä
Huomautus: Seuraavasta linkistä löytyvät usein kysytyt kysymykset funktioista ja päivämääristä SQL Serverissä: Yhteenveto
Tässä artikkelissa opimme, miten SQL muuntaa päivämäärän SQL Serverissä. Opimme, miten taulukon tietotyyppiä muutetaan ja miten CAST-, CONVERT- ja FORMAT-funktioita käytetään. Opimme myös SQL-tietotyyppien eri tyypeistä.
- Author
- Recent Posts
Hän on työskennellyt hallituksille, öljy-yhtiöille, verkkosivustoille, lehdille ja yliopistoille ympäri maailmaa. Daniel puhuu säännöllisesti myös SQL Servers -konferensseissa ja blogeissa. Hän kirjoittaa SQL Server -koulutusmateriaaleja sertifiointikokeisiin.
Hän auttaa myös SQLShack-artikkelien kääntämisessä espanjaksi
Katso kaikki käyttäjän Daniel Calbimonte viestit
- SQL-haastattelukysymyksiä – 3. syyskuuta, 2020
- SQL-sertifikaatit – 7. elokuuta 2020
- TSQL-historia – 3. syyskuuta 2019